Questions regarding SQL performance
Hi Experts,
I have the following questions re sql performance
1- When filtering for a column that is an int, and looking for values 0 or null, what is better for performance to write (columnname = 0 or columnname is null) or isnull(columnname,0)=0.
2- When having two or more conditions in the where clause, does it makes a difference which goes first?
3- Also in the where clause, when having multiple conditions from type where exists (select 1 from tablename where id = ...) and exists (select 1 from tablename where id = ...) and so on, does it makes a difference which table goes first?
4- And on above example is it better to have where exists (select 1 ...) or select null?
5- When checking for a single value is it ok to to write columnnane in ('value') or its better to have columnname = 'value'?
I have the following questions re sql performance
1- When filtering for a column that is an int, and looking for values 0 or null, what is better for performance to write (columnname = 0 or columnname is null) or isnull(columnname,0)=0.
2- When having two or more conditions in the where clause, does it makes a difference which goes first?
3- Also in the where clause, when having multiple conditions from type where exists (select 1 from tablename where id = ...) and exists (select 1 from tablename where id = ...) and so on, does it makes a difference which table goes first?
4- And on above example is it better to have where exists (select 1 ...) or select null?
5- When checking for a single value is it ok to to write columnnane in ('value') or its better to have columnname = 'value'?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scott's point about Q1 & indexes is very valid, my bad.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So here goes the SQL team-:)
@ScottPletcher,
Perhaps you have a script to verify that too?
Just to clarify, most likely match means the one that will narrow most the results?
Wouldn't it also depend on size of table's or other factors?
@ScottPletcher,
3. Perhaps. Put the most likely match first, down to the least likely.I see this is the topic you have different opinions..
Perhaps you have a script to verify that too?
Just to clarify, most likely match means the one that will narrow most the results?
Wouldn't it also depend on size of table's or other factors?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you guys, looking forward to work with you in my efforts to improve our database.
ASKER
Hi SQL Experts,
Perhaps you can help me with the issue below, very likely the solution lies in a SQL\Database setting..
https://www.experts-exchange.com/questions/28611342/make-recordset-read-only.html?anchorAnswerId=40609627#a40609627
Thanks,
Ben
Perhaps you can help me with the issue below, very likely the solution lies in a SQL\Database setting..
https://www.experts-exchange.com/questions/28611342/make-recordset-read-only.html?anchorAnswerId=40609627#a40609627
Thanks,
Ben
ASKER
Hi Experts,
When you have a chance, please take a look at the following.
https://www.experts-exchange.com/questions/28671817/SQL-Query-question.html
Thanks,
Ben
When you have a chance, please take a look at the following.
https://www.experts-exchange.com/questions/28671817/SQL-Query-question.html
Thanks,
Ben
ASKER
What about question#5 (added later)?
The reason for my asking this is that my SQL statement gets build dynamically and if I can have columnname in ('value') with no extra cost I would prefer that rather then having to determine how many values users have selected.
Is there a way to verify these things by sql plan or other SQL utility?