Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

More or less SARGABLE clauses in a WHERE Clause Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 12:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:52 AM
Points: 210, Visits: 378
Question - Is it better to have more SARGABLE Clauses in a WHERE Statement OR less to find a record(s).

Is it better to let the 2008/2012 optimiser choose the best plan from the available indexes or should you put in the least amount of clauses necessary to do the job. I remember in the old days you had to order your clauses with the most selective first and so on and I know that is not the case now and the optimiser is clever enough to know what to do however is there a rule of thumb or is it purely a case of experimentation with speed tests etc.

An example - thinking of a horse racing database - and I want to find last years Grand National at Aintree, which is a National Hunt race not a Flat race

Should I use WHERE Clause A or B? knowing that I have lots of combinations of indexes on my tables and that the record could be found any number of ways without a table / index scan.

In these examples I am using words in some places where I would use numeric IDs for ease of explanation.

a) less

WHERE RaceDate BETWEEN @StartDate AND @EndDate AND RaceName='Grand National' 

b) more

WHERE RaceDate BETWEEN @StartDate AND @EndDate AND RaceName='Grand National' AND CourseName = 'Aintree' AND RaceType = 'National Hunt'

The record can be identified by the first WHERE clause alone with an index of race titles AND date ranges BUT it can be narrowed down (in my head at least) quicker by thinking of just looking at National Hunt races at Aintree called the Grand National and then the date range.


By the way I am sure I used to know the answer to this but I've been very ill this year and all the meds I'm on are making my head very very fuzzy so I cannot recall for sure. I think a big chunk of my memory has been killed off by the National Health Service. Any help much appreciated.
Post #1388991
Posted Tuesday, November 27, 2012 12:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Rob Reid-246754 (11/27/2012)

Should I use WHERE Clause A or B?
obviously both are totally different as they contain different no and type of column's filer so i am 100 % sure that you wont get records or result EQUAL whenever u use them (if the table is haing DML operation) so there is not point to use A instead of B atleast for performance perspective BUT yes u might get same data at any time when any filter or condition is not returing the data.

Rob Reid-246754 (11/27/2012)

I have lots of combinations of indexes on my tables and that the record could be found any number of ways without a table / index scan.
So what first thing a lot of indexes DOESNT mean good performance as they required intensive maintenece practice and also increase resource usage.Another thing sql optimizer will decide whether there will be seek or scan will to get benefit for particular query.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1389001
Posted Tuesday, November 27, 2012 12:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Soon any expert will be here (i would expect gail ) to give you clear and better picture

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1389002
Posted Tuesday, November 27, 2012 1:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 39,866, Visits: 36,207
Write your queries to fetch just the data that you need. Fetching data to the front end that you don't need and discard is a waste of time, resources, network bandwidth, etc.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1389010
Posted Tuesday, November 27, 2012 2:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:51 AM
Points: 167, Visits: 683
HI,

just to make sure I understand:
You could essentially retrieve that unique record by using just 1 or 2 columns in your WHERE clause.
E.g.
SELECT EMP_ID, NAME FROM STAFF_LIST WHERE EMP_ID = 123

But the question is then: Would the performance of the query (i.e. the time it takes to return data) improve if you would add a bunch of columns to the WHERE clause to retrieve the same row, e.g.:
SELECT EMP_ID, NAME FROM STAFF_LIST WHERE EMP_ID = 123 AND FIRST_NAME = 'Eric' AND SURNAME = 'Mackin' AND EMP_ROLE = 'Analyst'

Although looking at the query execution plan and the associated stats would provide you with the answer, I would suggest that the more columns are referenced in your SELECT clause or WHERE clause, then the more data has to be transported over the network, the mroe data fetched from the disk, and the more CPU cycles to processes the extended clauses.

I think that the second query would be equivalent (performance/execution plan wise) to:
SELECT EMP_ID, NAME,  FIRST_NAME, SURNAME, EMP_ROLE FROM STAFF_LIST WHERE EMP_ID = 123

When it comes to the number of indexes on your table(s) - don't necessarily believe that the more indexes you have, the more your performance will suffer - although there is surely a happy mid point somewhere.
As it goes, the right answer is "it depends" - is your table heavily written to and read relatively infrequently? then small number of indexes is what you want as every write to the table involves a write to the indexes too. Is your table read from very often but not written to very often? then load the table up with indexes.

HTH,

B






Post #1389022
Posted Tuesday, November 27, 2012 2:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
bleroy (11/27/2012)
HI,

just to make sure I understand:
You could essentially retrieve that unique record by using just 1 or 2 columns in your WHERE clause.
E.g.
SELECT EMP_ID, NAME FROM STAFF_LIST WHERE EMP_ID = 123

But the question is then: Would the performance of the query (i.e. the time it takes to return data) improve if you would add a bunch of columns to the WHERE clause to retrieve the same row, e.g.:
SELECT EMP_ID, NAME FROM STAFF_LIST WHERE EMP_ID = 123 AND FIRST_NAME = 'Eric' AND SURNAME = 'Mackin' AND EMP_ROLE = 'Analyst'


I think that the second query would be equivalent (performance/execution plan wise) to:
SELECT EMP_ID, NAME,  FIRST_NAME, SURNAME, EMP_ROLE FROM STAFF_LIST WHERE EMP_ID = 123

i dont think , it only depends if the any one Non clustrered indexe has all columns from SELECT part plus where clause , other wise there could be key or RID lookup


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1389040
Posted Tuesday, November 27, 2012 3:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:52 AM
Points: 210, Visits: 378
I know about having lots of indexes being heavy on the writes but this is a read intensive process and I want to get a report for stats as quickly as possible.

The SELECTS are all in a stored proc writing to a table in that DB - no passing queries from a website to the DB or results back. It's a nightly job to fill a stats table that is then heavily read after from the DB itself.

I know about indexes but as this stats table has a lot of columns there are an appropriate number of indexes to cover the queries that are most heavily used as read time needs to be fast and far outweighs the cost of updating an index when inserting records which happens once a night.

A common query is by racename and date range and other common queries are by race type (NH or Flat) or by Course + race date. Therefore indexes exist on those columns and the data can be found by any mix of these indexes.

Therefore the question is not whether I am passing data too much data over the network or using too many indexes but whether the optimiser can decide for itself that too many clauses have been used and ignore the extra ones.

E.G an index on racename could be very big as you can get long textual strings up to 200+ chars although I could just use that clause I am guessing searching a larger/wider index takes longer than searching narrower ones.

Therefore by passing in courseID and RaceType would that help narrow down the results quicker before having to resort to the racename index OR should I just choose the quickest route even if it means using the biggest index to get there.

I hope I am being clear the big C is screwing me up.

Thanks for your help
Post #1389042
Posted Tuesday, November 27, 2012 3:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:51 AM
Points: 167, Visits: 683
Bhuvnesh

i dont think , it only depends if the any one Non clustrered indexe has all columns from SELECT part plus where clause , other wise there could be key or RID lookup


I think that regardless if the index has the additional columns included or not (using INCLUDE or otherwise), additional columns will generate additional data that has to be piped through one way or another - it doesn't really matter if that additional data comes from the index or from the source table.

Again, the rule really is: the less columns, the better - and I think (but can't confirm just now) that it doesn't matter if these columns are referenced in the WHERE clause or the SELECT statement in terms of performance.

B
Post #1389043
Posted Tuesday, November 27, 2012 3:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Rob Reid-246754 (11/27/2012)
whether the optimiser can decide for itself that too many clauses have been used and ignore the extra ones.
sql optimizer defintely choose only that index which is covering the most of the columns (which includes select + where + group by + order by columns ) and further more which index is less resource intensive too.

Rob Reid-246754 (11/27/2012)
Therefore by passing in courseID and RaceType would that help narrow down the results quicker before having to resort to the racename index OR should I just choose the quickest route even if it means using the biggest index to get there.
sql optimizer will choose the index not you (even if your not using any index HINT then) and for this if your post query + index definition then you can get more help.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1389045
Posted Tuesday, November 27, 2012 3:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 5,367, Visits: 9,913
Rob

I'm really sorry to hear about your health problems, and I hope it all works out for you.

It would really help if you could post the DDL for your table, including indexes and constraints. My first thought was that if the race is uniquely identified by its name ("Grand National") then you will have a unique constraint on it and therefore an index as well (assuming the database is properly designed), and it would be sufficient to search on that. Then it occurred to me that your table might store individual instances of the race (Grand National 2012, Grand National 2011 etc). So I think that the answer is indeed "it depends", but we can help a lot more if we know how your table is structured.

John
Post #1389050
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse