April 5, 2015 at 8:33 am
Hi Everyone,
Please see below query
Select ID, ID1,Name, Cname from Customer where ID =(@IDSearch) or ID1 =(@Idsearch)
When i run above query took around 3 mins, i have re-written query below,its taking around 1min...is there any way we can optimize query..! I don't have a option to create index..
Select ID, ID1,Name, Cname from Customer where ID =(@IDSearch)
UNION
Select ID, ID1,Name, Cname from Customer where ID1 =(@IDSearch)
Thanks in advance..if you have any questions or concerns please let me know
April 5, 2015 at 11:02 am
That query is about as optimal as it will get (and the OR will probably perform about the same), the cause of the poor performance is likely missing indexes. Can you get someone to create indexes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2015 at 7:20 pm
koti.raavi (4/5/2015)
Hi Everyone,Please see below query
Select ID, ID1,Name, Cname from Customer where ID =(@IDSearch) or ID1 =(@Idsearch)
When i run above query took around 3 mins, i have re-written query below,its taking around 1min...is there any way we can optimize query..! I don't have a option to create index..
Select ID, ID1,Name, Cname from Customer where ID =(@IDSearch)
UNION
Select ID, ID1,Name, Cname from Customer where ID1 =(@IDSearch)
Thanks in advance..if you have any questions or concerns please let me know
Hmmm.... is this actually a parent/child table where you're trying to find a given person and all the people that report to that person?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2015 at 3:36 am
koti.raavi (4/5/2015)
Hi Everyone,Please see below query
Select ID, ID1,Name, Cname from Customer where ID =(@IDSearch) or ID1 =(@Idsearch)
When i run above query took around 3 mins, i have re-written query below,its taking around 1min...is there any way we can optimize query..! I don't have a option to create index..
Select ID, ID1,Name, Cname from Customer where ID =(@IDSearch)
UNION
Select ID, ID1,Name, Cname from Customer where ID1 =(@IDSearch)
Thanks in advance..if you have any questions or concerns please let me know
Quick thought, I would normally bet on the OR as if the server has to scan the table the chances are that it will result in a single scan with two predicates rather than two scans on single predicates + concatenation
😎
Single scan with two predicates (OR)
+--------+ +----------------------+
| SELECT |<------| Clustered Index Scan |
+--------+ | Predicates: |
| ID |
| ID1 |
+----------------------+
Two scans on single predicates (UNION)
+--------+ +---------------+ +----------------------+
| SELECT |<------| Concatenation |<----------------| Clustered Index Scan |
+--------+ | |<--------, | Predicates: |
+---------------+ | | ID |
| +----------------------+
|
| +----------------------+
'-------| Clustered Index Scan |
| Predicates: |
| ID1 |
+----------------------+
April 6, 2015 at 8:02 am
I would guess that the best solution is to create an index to cover the query. If you can't create them, who's in charge of the database? Someone must be able to. However, without any DDL or knowledge of anything that exists, there's no way to be sure.
April 6, 2015 at 10:07 pm
Eirikur Eiriksson (4/6/2015)
koti.raavi (4/5/2015)
Hi Everyone,Please see below query
Select ID, ID1,Name, Cname from Customer where ID =(@IDSearch) or ID1 =(@Idsearch)
When i run above query took around 3 mins, i have re-written query below,its taking around 1min...is there any way we can optimize query..! I don't have a option to create index..
Select ID, ID1,Name, Cname from Customer where ID =(@IDSearch)
UNION
Select ID, ID1,Name, Cname from Customer where ID1 =(@IDSearch)
Thanks in advance..if you have any questions or concerns please let me know
Quick thought, I would normally bet on the OR as if the server has to scan the table the chances are that it will result in a single scan with two predicates rather than two scans on single predicates + concatenation
😎
Single scan with two predicates (OR)
+--------+ +----------------------+
| SELECT |<------| Clustered Index Scan |
+--------+ | Predicates: |
| ID |
| ID1 |
+----------------------+
Two scans on single predicates (UNION)
+--------+ +---------------+ +----------------------+
| SELECT |<------| Concatenation |<----------------| Clustered Index Scan |
+--------+ | |<--------, | Predicates: |
+---------------+ | | ID |
| +----------------------+
|
| +----------------------+
'-------| Clustered Index Scan |
| Predicates: |
| ID1 |
+----------------------+
Is it possible that the engine can use two threads simultaneously to scan the table in the Union scenario? I've learned and seen the Union perform better than the OR (at least when many OR's are involved ).
----------------------------------------------------
April 7, 2015 at 2:37 am
MMartin1 (4/6/2015)
Is it possible that the engine can use two threads simultaneously to scan the table in the Union scenario? I've learned and seen the Union perform better than the OR (at least when many OR's are involved ).
If the optimiser decides to parallel the query, then both options can use multiple threads. If the optimiser decides to run the query serially, then both options use a single thread.
Many of the 'use union rather than or' come from SQL 2000 or before where the optimiser had limited options for processing an OR and multiple options for a union. Post SQL 2000, most poor OR performance is incorrect indexing (you need one index for EACH column referred to in a predicate within the OR).
If the indexes are inadequate, the union version is probably the more expensive (two scans rather than the one for OR) and hence will tip to running in parallel earlier.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2015 at 8:03 am
And now for the biggest question of them all...
Where the hell did the OP disappear to? :crazy:
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2015 at 8:49 am
April 7, 2015 at 11:18 am
First Apologies for delay in response:
Firstly Thanks to
GilaMonster,Jeff Moden,Eirikur Eiriksson,CELKO,Ed Wagner,MMartin1,MadAdmin for your valuable time
--Jeff I'm here 🙂
Yes, if we use 2 unions it will scan the table twice, but i have noticed couple of times UNION is much better than OR condition..that's why i have posted this..GilaMonster may be correct as both queries are executing parallel. I have noticed one thing today, index is already exist on table but its not using existing index.. Existing index is combination of columns (Multiple columns -Non Clustered Index). table is already contain clustered index too...when i executed estimated execution plan it saying create stand alone index on column....i think its problem with order of indexes, even i have used WITH INDEX option to force table to use index. but no use .. Below is the example how indexes are created on table....Thank you again...and if you have any questions please let me know
Ex: CM_Number (Primarykey Clustered)
CM_Number,ID,ID1, Customer Name (NonClustered Index)
April 7, 2015 at 11:35 am
Well, the existing indexes wont help.
You need 2 indexes. One on ID1 and one on ID2.
Depending on cardinality, the original query should be instant if both the indexes exist
, i.e. not one index with both columns, 2 separate indexes, one index with ID, and one with ID1.
So it all depends how important the query is.
You can create 2 indexes on a live system with online = on if you have enterprise.
Else, you need down time, depending on whether your CR approvers think it is important enough.
The first column needs to be sorted on the where clause condition.
Conceptually, you can test it out in excel, put in a large data set into excel and sort by column 1.
Then figure out how to get certain data from column 2 using the data with sorted column1 as is.
Can't be done without reading all values from top to bottom.
You need a copy of the column you are searching on, sorted. (i.e. an index on ID) to get those few records.
THEN
You need a separate copy of the other column you are searching on, sorted. (i.e. an index on ID1) to get those few records.
Incidentally, your non clustered index is pointless and probably worse than your clustered index since you clustered index is unique, so the optimiser knows it will only get one row from the PK if you query on the PK, unless you are doing a index scan on the nonclustered index.
April 7, 2015 at 11:46 am
HI , I don't know what is the use of Included columns or Creating index on multiple columns..if we create 2 separate indexes obliviously we will see 99% performance improvement...but there are 70 reports running on one table..that table contain 80 columns...almost 60 columns used in where clause criteria...if this is the case do we need to create 60 indexes?...what is your thoughts on this?...all where clause cafeterias are stand alone ...
Ex : Query1 : Select * from t1 where Column1=@C1
Query2 : Select * from t1 where Column2=@C2
Query3 : Select * from t1 where Column3=@C3
Query4 : Select * from t1 where Column4=@C4
Query5 : Select * from t1 where Column5=@C5
April 7, 2015 at 12:02 pm
Your OR condition makes it impossible for one index to satisfy all the conditions.
You need an AND for composite indexes.
select from tableA where COL1 = @col and Col2= @col2.
Then you CAN create an index on both COL1 and COL2, but if the cardinality on COL1 is very high, or even unique, THEN you dont even need a combo index.
I would look at the stats of how the reports are queried.
The missing index views will give you a hint as to which combinations are most called, and what the cost of the query currently is.
If you use SSRS, there is a table which gives you the reports which have been executed with the parameters that they were executed with and their duration.
Then I would look and see which queries are most often called, with which parameters.
Then it is a case of evaluating if the table can take the transactional overhead of additional indexes.
Then you can create the least number of indexes which gives most benefit to most users most of the time.
I would first use bare minimum indexes without includes in QA just to see if it works at all.
But don't worry about composite indexes for this scenario UNLESS you have a where clause which has an AND.
Additionally, don't worry about includes unless lookups are a problem.
Your first point of call is to make the query use an index.
Then you can worry about making the query hit the index without doing lookups.
What you can do is see if you can give us the proc which fires off the SQL with all the OR conditions. There may be something which can be done on the proc instead of the DB data structures.
April 7, 2015 at 12:21 pm
Okay Thank you very much for your reply..I have small question here..its different from previous question
Scenario : Below query is searching for Member_Id
Ex: Select c1,c2 from t1 where Member_ID=@MemberID
When i execute execution plan it says create non clustered index on Member_ID to improve 99.212 performance. but below are the indexes created on table
Index1: ID (PK-Clustered Index)
Index2: ID,Member_ID,Memeber_Name
As you can see Index is already exist on member_id column, but query plan says create standard alone index...is there way we utilize existing instead of creating one more index?..even i have used with (Index Option) to utilize existing index (forcing table to use existing index)..thanks
April 7, 2015 at 2:09 pm
The index is (ID,Member_ID,Memeber_Name)
This means that it is like an excel spreadsheet sorted on ID, THEN by Member_id.
So you dont have an index on Member_id.
Conceptually, you have an index on firstname, then Second name, then surname.
Andrew Ben Zacharias
Andrew Ken Adams
Xavier Penn Zacharias.
So Andrew Ben Zacharias may be the first row in the table.
This does not mean you can search for Zacharias quickly.
If you try and search for all surnames = Zacharias, the surname column is not sorted. You have Zacharias on first row, and Zacharias on last row.
To get Zacharias with this index, you have to go first row to last row one by one.
What if your table has millions of rows?
Massive scan.
You need to sort your data like so.
Adams Andrew Ben
Adams Andrew Ken
Zacharias Andrew Ben
Zacharias.Xavier Penn
Surname first, then SQL can do a btree seek to find Zacharias, and when it hits Zadebe, it will know it is complete and not have to go further.
Now, if you replace surname with MemberNo, and firstname with ID, then you can see the problem.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply