comparing query results

1. What is the best way to compare efficiency (performance) of two different
queries that return the same result in general?
2. What if one contains user defined function one query and another without
user defined function? The reason I am asking this is that the optimizer's
cost model do not reflect the work done in UDF in the query execution plan.
ThanksJustin
Compare both exection plans first. In my experience ( sure it depends) that
queries which don't no UDF/s perform better
"Justin" <nospam@.nospam.com> wrote in message
news:O%23$Nz46jGHA.4828@.TK2MSFTNGP04.phx.gbl...
> 1. What is the best way to compare efficiency (performance) of two
> different queries that return the same result in general?
> 2. What if one contains user defined function one query and another
> without user defined function? The reason I am asking this is that the
> optimizer's cost model do not reflect the work done in UDF in the query
> execution plan.
> Thanks
>|||I'd like to use Profiler to see reads etc. Profiler *does* include the work
done inside an UDF
(something that execution plan or STATISTICS IO doesn't do).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Justin" <nospam@.nospam.com> wrote in message news:O%23$Nz46jGHA.4828@.TK2MSFTNGP04.phx.gbl.
.
> 1. What is the best way to compare efficiency (performance) of two differe
nt queries that return
> the same result in general?
> 2. What if one contains user defined function one query and another withou
t user defined function?
> The reason I am asking this is that the optimizer's cost model do not refl
ect the work done in UDF
> in the query execution plan.
> Thanks
>|||Justin a crit :
> 1. What is the best way to compare efficiency (performance) of two differe
nt
> queries that return the same result in general?
> 2. What if one contains user defined function one query and another withou
t
> user defined function? The reason I am asking this is that the optimizer'
s
> cost model do not reflect the work done in UDF in the query execution plan
.
> Thanks
>
The most effective parameters in order are :
1) IO, so execute your queries with this flag set :
SET STATISTICS IO ON /OFF
2) the time taken by CPU and other subsystems, so
SET STATISTICS TIME ON
The exection plan is not a good indicator because it is not quiclky
readable and the number of basic steps is not correlated with time or IO
consummation.
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Comparing individual performance to overall average

Hello. I'll begin by stating that I've only been using BusinessObjects Crystal Reports XI for a short amount of time--somewhere around two months. This problem is rather frustrating as I'm sure there must be a more direct means of solving it (i.e. avoiding subreports and hackish workarounds of printtime formula evaluations, etc...)

Briefing: I am connecting to a simple single-element Business View (on top of MS SQL Server). Each record in the view is a trouble ticket handled by my group at work. Using simple date arithmetic, I am able to determine how long it takes for each trouble ticket to go from an open state to a closed state. Naturally, it is crucial to know how quickly these tickets are being resolved.

All is simple, so far. The objective behind the report in question is to analyze how long, on average, one client's tickets take to close against the average for all clients--grouped by fiscal quarter. The company/companies to compare against the overall average are determined by a parameter.

To restate: I have one level of grouping for fiscal Quarter, beginning with 2005 Q1. Under this I need to group by Company, and this needs to be based on the aforementioned string array parameter. The trick is that I also need to preserve the data for all clients while simultaneously isolating the selected customers. The data will be used to make a multi-bar graph.

So...how do I go about grouping and sorting and selecting and whatnot as to achieve my desired effect? As I alluded to above, I feel that there is a convoluted way to approach this problem, and Im looking for some wise soul to inject some wisdom.

Any and all help would be much appreciated.EDIT:

I've solved the problem. If anyone would like to know how, simply ask.sqlsql

Comparing datetime fields results in slow query performance

Hi,
I am currently deduping a data warehouse containing around 3 million
records. Most of my dedupe scripts run in 3 or 4 minutes but the
scripts which compare datetime fields take up to 3 hours. I have a
non-clustered index on the date column. Can anybody offer any advice on
how I might improve query times please?
Thanks,
Charlie.which version of SQL Server? (2000 or 2005)
what the index plan says?
what type of comparison do you do?
what the index tunning wizard says against your query?
comparing date/time is slower then comparing integer.
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegroups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>|||In addition to the previous post, my guess is you use the columns inside an
expression, so they are not Searchable ARGuments (SARGs) anymore. Try to
rewrite the queries to have the indexed datetime columns without an
expression in the Where clause. For example:
create table a
(a datetime)
create index aa on a(a)
insert into a values ('2006-01-16')
insert into a values ('2006-01-17')
go
select a from a
where datediff(dd,a,getdate()) < 2 -- this query should do an index scan
select a from a
where dateadd(dd,-1,convert(char(10),getdate(),112)) = a -- this query
should do an index seek
However, take care you get correct results - don't forget that you always
have time part in the datetime data. You should check if the Between
operator would be useful for you.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegroups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>

Comparing datetime fields results in slow query performance

Hi,
I am currently deduping a data warehouse containing around 3 million
records. Most of my dedupe scripts run in 3 or 4 minutes but the
scripts which compare datetime fields take up to 3 hours. I have a
non-clustered index on the date column. Can anybody offer any advice on
how I might improve query times please?
Thanks,
Charlie.
which version of SQL Server? (2000 or 2005)
what the index plan says?
what type of comparison do you do?
what the index tunning wizard says against your query?
comparing date/time is slower then comparing integer.
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegr oups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>
|||In addition to the previous post, my guess is you use the columns inside an
expression, so they are not Searchable ARGuments (SARGs) anymore. Try to
rewrite the queries to have the indexed datetime columns without an
expression in the Where clause. For example:
create table a
(a datetime)
create index aa on a(a)
insert into a values ('2006-01-16')
insert into a values ('2006-01-17')
go
select a from a
where datediff(dd,a,getdate()) < 2 -- this query should do an index scan
select a from a
where dateadd(dd,-1,convert(char(10),getdate(),112)) = a -- this query
should do an index seek
However, take care you get correct results - don't forget that you always
have time part in the datetime data. You should check if the Between
operator would be useful for you.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegr oups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>
sqlsql

Compare BULK INSERT vs INSERT

Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT vs INSERT? Performance speaking, which operations is generally faster given the same amout of data inserted.

Sincerely,
-Lawrence

I don't know anything about the transactions logs.

Bulk insert is generally much faster.

Jonathan

|||

BULK INSERT can be a minimally logged operation (depending on various parameters like indexes, constraints on the tables, recovery model of the database etc). Minimally logged operations only log allocations and deallocations. In case of BULK INSERT, only extent allocations are logged instead of the actual data being inserted. This will provide much better performance than INSERT. You can start with the links below for more information:

http://msdn2.microsoft.com/en-us/library/ms190421.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx

Compare BULK INSERT vs INSERT

Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT
vs INSERT? Performance speaking, which operations is generally faster given
the same amout of data inserted.
Sincerely,
-LawrenceIf conditions are right (e.g. destination table is not replicated), BULK
INSERT can exploit minimal transaction logging for performance advantage.
Linchi
"Lawrence" wrote:
> Hello,
> I am wondering is the Transaction Log logged differently between BULK INSERT
> vs INSERT? Performance speaking, which operations is generally faster given
> the same amout of data inserted.
> Sincerely,
> -Lawrence
>|||I should add that in SQL2005, INSERT can make use of the bulk rowset provider
in the OpenRowset function. But I have not tested the performance difference,
if any, between BULK INSERT and INSERT ... SELECT FROM OpenRowSet(bulk...).
Linchi
"Linchi Shea" wrote:
> If conditions are right (e.g. destination table is not replicated), BULK
> INSERT can exploit minimal transaction logging for performance advantage.
> Linchi
> "Lawrence" wrote:
> > Hello,
> > I am wondering is the Transaction Log logged differently between BULK INSERT
> > vs INSERT? Performance speaking, which operations is generally faster given
> > the same amout of data inserted.
> >
> > Sincerely,
> > -Lawrence
> >|||"Lawrence" <Lawrence@.discussions.microsoft.com> wrote in message
news:097C24AA-AE0A-45DC-9C2D-B09AAD9A4539@.microsoft.com...
> Hello,
> I am wondering is the Transaction Log logged differently between BULK
INSERT
> vs INSERT? Performance speaking, which operations is generally faster
given
> the same amout of data inserted.
>
BULK INSERT can be incredibly fast compared to INSERT, simply because of how
it can handle the logging.
> Sincerely,
> -Lawrence
>

Compare BULK INSERT vs INSERT

Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT
vs INSERT? Performance speaking, which operations is generally faster given
the same amout of data inserted.
Sincerely,
-LawrenceIf conditions are right (e.g. destination table is not replicated), BULK
INSERT can exploit minimal transaction logging for performance advantage.
Linchi
"Lawrence" wrote:

> Hello,
> I am wondering is the Transaction Log logged differently between BULK INSE
RT
> vs INSERT? Performance speaking, which operations is generally faster giv
en
> the same amout of data inserted.
> Sincerely,
> -Lawrence
>|||I should add that in SQL2005, INSERT can make use of the bulk rowset provide
r
in the OpenRowset function. But I have not tested the performance difference
,
if any, between BULK INSERT and INSERT ... SELECT FROM OpenRowSet(bulk...).
Linchi
"Linchi Shea" wrote:
[vbcol=seagreen]
> If conditions are right (e.g. destination table is not replicated), BULK
> INSERT can exploit minimal transaction logging for performance advantage.
> Linchi
> "Lawrence" wrote:
>|||"Lawrence" <Lawrence@.discussions.microsoft.com> wrote in message
news:097C24AA-AE0A-45DC-9C2D-B09AAD9A4539@.microsoft.com...
> Hello,
> I am wondering is the Transaction Log logged differently between BULK
INSERT
> vs INSERT? Performance speaking, which operations is generally faster
given
> the same amout of data inserted.
>
BULK INSERT can be incredibly fast compared to INSERT, simply because of how
it can handle the logging.

> Sincerely,
> -Lawrence
>

Compare 2000 with 2005 Performance - 32/64 Decide to buy?

I need to show my boss that 2005 will on average be faster than 2000.

Are there any performance benchmark results available to show this?

Also I need similar benchmarks to show 64 bit will be faster than 32 bit SQL 2005.

Ian

Try:

http://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/SQLServer2005_WhyUpgrade.doc

|||I'm looking specifically for the answer to," Why move to 64 bit from 32 bit"

The document is good, but only addresses moving from 2000 to 2005 and not justification of moving hardware to 64 bit.

Ian

|||The main reason to go 64bit is better memory performance for large amounts of memory (ie 16gb+), and better CPU performance for 64bit applications. The other reason is, everything will be 64bit at some time. It is the future.

Now the problems. The main problem you will have with 64bit is with ODBC drivers and other 3rd party drivers not supporting 64bit.

Before you even entertain the idea of changing, check to make sure everything you run on the server is compatable and SUPPORTED in Windows 2003 64bit.

|||

FYI

I have a few 2005 (SS2k5 x64) installs and many 2000 (SS2K x32) installs.

Last evening a developer emailed and said her select was taking to long to run, 22 minutes on the SS2K x32. I cut\pasted her select and executed it on a new X64 Sql Server 2005 SP2 (3050). The databases are identical as the X64 will be her new Sql server.

The sql ran in 36 seconds.

I look good

|||

See if this helps:

http://www.microsoft.com/sql/editions/64bit/default.mspx

Compare 2000 with 2005 Performance - 32/64 Decide to buy?

I need to show my boss that 2005 will on average be faster than 2000.

Are there any performance benchmark results available to show this?

Also I need similar benchmarks to show 64 bit will be faster than 32 bit SQL 2005.

Ian

Try:

http://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/SQLServer2005_WhyUpgrade.doc

|||I'm looking specifically for the answer to," Why move to 64 bit from 32 bit"

The document is good, but only addresses moving from 2000 to 2005 and not justification of moving hardware to 64 bit.

Ian

|||The main reason to go 64bit is better memory performance for large amounts of memory (ie 16gb+), and better CPU performance for 64bit applications. The other reason is, everything will be 64bit at some time. It is the future.

Now the problems. The main problem you will have with 64bit is with ODBC drivers and other 3rd party drivers not supporting 64bit.

Before you even entertain the idea of changing, check to make sure everything you run on the server is compatable and SUPPORTED in Windows 2003 64bit.

|||

FYI

I have a few 2005 (SS2k5 x64) installs and many 2000 (SS2K x32) installs.

Last evening a developer emailed and said her select was taking to long to run, 22 minutes on the SS2K x32. I cut\pasted her select and executed it on a new X64 Sql Server 2005 SP2 (3050). The databases are identical as the X64 will be her new Sql server.

The sql ran in 36 seconds.

I look good

|||

See if this helps:

http://www.microsoft.com/sql/editions/64bit/default.mspx

Comparative training performance

Recently I started evaluating and studying SQLServer2005 Datamining capabilities, I must say that I like the simplified integration between the database and data mining world.

I was looking for any study on comparative model training performance between competitive products (SAS etc) and SQLServer2005 datamining algorithms.

Thanks

Rajeev Gupta

We don't have any comparative studies (for many reasons), these kind of studies would have to be done by a third party, and would in general be difficult to do side by side comparisons since the products in general do different things.

For example, we're optimized for processing multiple models at the same time on large data, which impacts performance on processing a single model on small data. Not saying we're slow on the latter case, but it's hard to make apples to apples comparisons.

Committed Memory

I recently did some performance testing and one of the
counters I monitored was Committed memory. Can someone
explain what this means. Is it the amount SQL Server can
access?
Also when AWE is enabled the memory used in Task manager
goes to whatever the limit is for SQL Server. Is that
because it is reserved for SQL Server processes.
Thanks for any advice.
JamieWhen you use AWE memory with SQL Server, SQL statically maps all of the
memory at startup (unlike nonAWE memory which is dynamic). If you want to
reserve less memory for SQL and leave more memory for other apps, use the
"sp_configure max server memory" option. Books on Line has a good
description under "Managing AWE Memory".
Brian Goldstein
SQL Server Product Group
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Jamie Downs" <jamie@.and.co.uk> wrote in message
news:09b801c33fb5$93e73720$a101280a@.phx.gbl...
> I recently did some performance testing and one of the
> counters I monitored was Committed memory. Can someone
> explain what this means. Is it the amount SQL Server can
> access?
> Also when AWE is enabled the memory used in Task manager
> goes to whatever the limit is for SQL Server. Is that
> because it is reserved for SQL Server processes.
> Thanks for any advice.
> Jamie

Comments in SP

Hi,
I was wondering if the amount of comments inside a stored procedure could
hinder in any way the performance of the same sp. Does anyone know?
regards,AFAIK, Nope they have no impact on performance ...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Emmanuel" <emmanuel@.email.com> wrote in message
news:u%23PZw1zEFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I was wondering if the amount of comments inside a stored procedure could
> hinder in any way the performance of the same sp. Does anyone know?
>
> regards,
>
>|||Apart from the time the compiler takes to parse the symbols in the procedure
source.
This is almost negligable and certainly discountable for the benefits of
having comments in your code. If it needs an essay the its probably better
in an external file with a reference note in the sproc header.
Mr Tea
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:cusgo5$h7q$1@.news01.intel.com...
> AFAIK, Nope they have no impact on performance ...
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
> "Emmanuel" <emmanuel@.email.com> wrote in message
> news:u%23PZw1zEFHA.732@.TK2MSFTNGP12.phx.gbl...
could
>|||While i haven't done any tests yet I suspect that lots of comments can
indeed affect performance on a system with lots of sp's. The reason being
is that the comments are also stored in the syscacheobjects when the plan is
created. This has several effects. One is that it takes up more memory for
the procedure cache. It also takes a little more effort to create the hash
code and do the searching in the cache once the hash bucket is identified.
Don't take me wrong here, I am not advocating removing all comments<g>.
But I have seen some large systems that had massive amounts of comments that
when added up, was well over 100MB's in coments. That is memory that could
be used by other processes. The bottom line is that most systems will never
notice any difference if they removed all their comments. But technically I
believe it can potentially affect performance.
Andrew J. Kelly SQL MVP
"Emmanuel" <emmanuel@.email.com> wrote in message
news:u%23PZw1zEFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I was wondering if the amount of comments inside a stored procedure could
> hinder in any way the performance of the same sp. Does anyone know?
>
> regards,
>
>