SQL Server Performance

Question

0.00/5 (No votes)

See more:

Can anyone explain to me why a query that returns 0 rows takes a long time to execute?
It returns about 50 columns... It seems that the number of columns returned has a HUGE effect much more than I would expect since I am getting no rows. The query takes a full second to run unless I slim the number of columns...
Additional information none of the columns are large data types mostly integers and small strings.

Posted 15-Dec-14 5:36am

brianriggan

Add a Solution

Comments

Maciej Los 15-Dec-14 12:34pm

Why to execute query which returns 0 rows? Could you, please, elaborate?

brianriggan 15-Dec-14 12:37pm

It does not always return 0 rows.. I was just using that as an example because usually slowness comes from the number of rows returned.. in this case the slowness is being caused by something else.

Jörgen Andersson 15-Dec-14 12:47pm

Would you mind posting the query?

3 solutions

Solution 3

There's not enough info to give you a proper answer, but I can point you to an article[^] from one of the old Masters

Posted 15-Dec-14 6:50am

Jörgen Andersson

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shweta N Mishra · Accepted Answer · 2014-12-15T05:43:00

Solution 1

Though it returns 0 rows but to get 50 columns it has to go to all those and read and hence the issue.

you may wanna refer to Improving SQL Server Performance

and look for topic Return Only the Rows and Columns Needed in above link.

Posted 15-Dec-14 5:43am

Shweta N Mishra

Updated 15-Dec-14 5:44am

v3

Comments

brianriggan 15-Dec-14 11:46am

The problem is that I do need all of the columns, I have slimmed down the result set as much as possible the original author was using SELECT * I am only getting what is needed... I just need a large number of rows. Is there a setting in SQL server so that I could increase the memory used for a query or something?

Shweta N Mishra 15-Dec-14 11:48am

does your table has proper indexes ?

How much records are there in table your are working with ?, Are there partition created ?

brianriggan 15-Dec-14 11:51am

The table is indexed as it should be.
About 300,000...
There are not partitions this is a subject I am not very familiar with.. We do not have a "real" dba so I think this might be a configuration issue that has not been addressed. Also the number of fields are a requirement in this case... Another part of this puzzle.. The query is a parameterized string the query executes sub second very fast in SQL management studio but takes a full second to execute the same query using a data reader in C#.

Kornfeld Eliyahu Peter · Accepted Answer · 2014-12-15T05:52:00

How a SQL query performs is depend on a lot of factors - the volume of the data it returns not one of them (it affects the network traffic)...
1. Joins in the query
2. Indexes on involved tables
3. Type of available indexes (clustered, non-clustered)
4. Choose sub-query over join and vica versa...
5. The actual build-up of the tables involved (data-page size, index-page size...)
This wisdom can not be taught in a sort answer like here, but you can research the subject:
http://technet.microsoft.com/en-us/library/ms190610(v=SQL.105).aspx[^]
http://msdn.microsoft.com/en-us/library/ff647793.aspx[^]

SQL Server Performance

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

SQL Server Performance

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us