November 16, 2016 at 10:18 am
Hi,
I have the following query that is returning 1.6 M rows and about 300 columns. I can't change this factor. Here is the current
query
select Column1 ... Column290
from dob.table1
where (ISNULL(Column50, 1) = 1)
I read that ISNULL has a performance hit in the where clause. I tried moving the ISNULL to the Select but not improvement.
I also tried where Column50 = 1 OR Column50 is null but not improvement.
I read that computed columns may be an option to help.
Can anyone give me a sample base on my query using computed column to tryout?
Also, I added index and filter index but also still no improvement. Currently the time is about 9 minutes. I am trying to shave a 1 minute off the time.
Any help is greatly appreciated.
Thanks for any help.
November 16, 2016 at 10:32 am
Isn't this functionally the same ?
WHERE Column50 IS NULL
It appears that you are testing for nulls incorrectly.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 16, 2016 at 10:43 am
Have you tried creating a non-clustered index on Col50.
November 16, 2016 at 10:53 am
currently added non-unique, non-clustered index and filter index
I got the 9 min query time without index and using ISNULL in the where
but so far, I change the where clause and added the index, I have been unsuccessful in reducing the query time.
November 16, 2016 at 11:08 am
comic_rage (11/16/2016)
Hi,I have the following query that is returning 1.6 M rows and about 300 columns. I can't change this factor. Here is the current
query
select Column1 ... Column290
from dob.table1
where (ISNULL(Column50, 1) = 1)
I read that ISNULL has a performance hit in the where clause. I tried moving the ISNULL to the Select but not improvement.
I also tried where Column50 = 1 OR Column50 is null but not improvement.
I read that computed columns may be an option to help.
Can anyone give me a sample base on my query using computed column to tryout?
Also, I added index and filter index but also still no improvement. Currently the time is about 9 minutes. I am trying to shave a 1 minute off the time.
Any help is greatly appreciated.
Thanks for any help.
Unless you are returning only a small percentage of the rows of the table, you're not going to see much improvement.
The filter is equivalent to (column50 IS NULL OR column50 = 1). It isn't SARGable (can't be helped by an index) - and a covering index would require INCLUDEing all those columns.
What consumes the data?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
November 16, 2016 at 11:10 am
The way to add a calculated fields is like this
CREATE TABLE dbo.Table1 (
Col1 ...
, Col2 ...
, .....
, SearchCol AS ISNULL(Col50, 1) PERSISTED
);
GO
CREATE NONCLUSTERED INDEX ix_Table1_SearchCol
ON dbo.Table1 (SearchCol)
WHERE SearchCol = 1;
GO
SELECT *
FROM dbo.Table1
WHERE SearchCol = 1;
That said, I'm not sure if it will improve your timings.
What is the 9 minutes for
- Returning the data to the screen?
- Returning the data to an application?
How are you measuring the 9 minutes?
November 16, 2016 at 11:21 am
I am measuring the time on SSMS.
November 16, 2016 at 11:25 am
comic_rage (11/16/2016)
I am measuring the time on SSMS.
What will consume the result set?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
November 16, 2016 at 11:30 am
the data is binded to a asp.net gridview.
November 16, 2016 at 11:51 am
comic_rage (11/16/2016)
I am measuring the time on SSMS.
How are you measuring this?
Is this the time to insert the data into another table?
or the time to allocate the data to a set of variables?
or the time to return the data to the display?
November 16, 2016 at 11:53 am
comic_rage (11/16/2016)
the data is binded to a asp.net gridview.
Why on earth would you want to bind millions of rows of data to a gridview?
It's not humanly possible to read and comprehend that data.
November 16, 2016 at 11:56 am
deleted ... somehow my post was duplicated
November 16, 2016 at 12:13 pm
comic_rage (11/16/2016)
Hi,I have the following query that is returning 1.6 M rows and about 300 columns. I can't change this factor. Here is the current
query
select Column1 ... Column290
from dob.table1
where (ISNULL(Column50, 1) = 1)
I read that ISNULL has a performance hit in the where clause. I tried moving the ISNULL to the Select but not improvement.
I also tried where Column50 = 1 OR Column50 is null but not improvement.
I read that computed columns may be an option to help.
Can anyone give me a sample base on my query using computed column to tryout?
Also, I added index and filter index but also still no improvement. Currently the time is about 9 minutes. I am trying to shave a 1 minute off the time.
Any help is greatly appreciated.
Thanks for any help.
First, if most of the rows in this table meet the inclusion criteria, then non-clustered index and bookmark operation will not be beneficial, and SQL Server will probably ignore the index and default to a full table scan. Also, considering you are returning 300 columns, then a covering non-clustered index is not an option.
However, if what you are returning is a small subset of the total rows, then try creating a non-clustered index on Column50. That That expression "(ISNULL(Column50, 1) = 1)" may not be sargable, so modify your WHERE clause to the following:
where Column50 is null or Column50 = 1
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 16, 2016 at 12:15 pm
DesNorton (11/16/2016)
deleted ... somehow my post was duplicated
Because it needed saying twice?
Assuming the gridview can handle this much data, it's got to get there down the wire then presumably get filtered & sorted.
Far better to filter in the query, down to a manageable level. Or even page it.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
November 16, 2016 at 12:30 pm
was able to improve the query by moving the isnull to the select and adding the column to a filtered index. Was able to reduce query time from 9+ min to 7+ min. Also changed the where clause to something like column = 1. As many posts have stated, avoid using ISNULL in the whereclause.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply