Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2012-06-14 : 16:27:40
|
| I'm pretty sure there's not a simple answer to this.I have a query that takes at least 60 seconds when using WHERE CustName = 'ABC' but only takes 4 seconds when using WHERE CustName LIKE 'ABC'. In the first case it does index scans on a few non-clustered indexes on TableA. In the second case it does clustered index seeks on TableA. The query comes from Microsoft Dynamics GP so I have no control over it, I can only control the indexes. I'd post some schema and the query but there are about 20 tables involved with tons of columns and there are nested views and so forth. I guess I'm just wondering if there's a way to get the database engine to use the clustered index without deleting all the non-clustered indexes. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-14 : 16:51:15
|
| Are you sure the statistics are fully updated (FULLSCAN) on all the relevant tables? I'd also suggest running sp_recompile against those tables, if you can take a temporary performance hit (Or even DBCC FREEPROCCACHE) and see if the plans change or performance improves.Typically a LIKE without wildcard characters gets translated to equals by the optimizer. |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-06-14 : 17:18:59
|
| Great ideas! But they didn't change anything. Any other ideas? If I remove two of the non-clustered indexes it starts using the clustered index but (a) I know those non-clustereds are used by other queries, and (b) it's still only doing a scan instead of a seek and still taking a long time. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-14 : 17:30:33
|
| Well if you can't change the queries provided by Dynamics I don't recommend removing any indexes originally installed with it, unless you find they're completely unused (seeks + scans + lookups = 0).My next suggestion is to consider Plan Guides: http://technet.microsoft.com/en-us/library/ms190417.aspxI personally have never used them and got zero response at a conference when I asked if others were using them.How many rows are being returned? Are you querying for ABC often, or is that just an example? Do you get the same behavior for different values? |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-06-14 : 18:17:33
|
| ABC is an example since I can't mention any real names. I get fast query times/plans with seeks with any other company name I try, it's only ABC that's slow. ABC returns around 500 rows, the others anywhere from 0 to 10,000, but ABC is the only one that's slow. The plan guide idea might work, I'm attempting that now. |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-06-14 : 20:09:53
|
| I got it working using the plan guide! Thanks robvolk! |
|
|
|
|
|
|
LIKE faster than equality check