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 |
|
Gratin
Starting Member
6 Posts |
 Posted - 2012-01-31 : 08:19:37
|
| Good afternoon,There is a Table with 450 000 records, basicly it is a list of files and folders path. I have a working Select Stored procedure however i would like to optimize it. Where do you think it could be improved ?Thx,Phil.SELECT RIGHT( rtrim(IndexFileName), len(rtrim(IndexFileName))-2) as FolderFileName , max(RIGHT(rtrim(IndexFileName), CHARINDEX('\',REVERSE(rtrim(IndexFileName)))-1)) as FileName , max(case when left(IndexFileName, 2) = 'T:' then IndexFileLastWrite end) as T , max(case when left(IndexFileName, 2) = 'U:' then IndexFileLastWrite end) as U , max(case when left(IndexFileName, 2) = 'V:' then IndexFileLastWrite end) as V FROM [tblIndex] WITH (INDEX(PK_IndexFileName))WHERE @booFolder = CHARINDEX('\', RIGHT(rtrim(IndexFileName), 1)) and (indexFileName like '%' + @txtSearch + '%') and LEN(IndexFileName)- LEN(replace(indexfilename,'\','')) = @intLevel + @booFolder GROUP BY RIGHT( rtrim(IndexFileName), len(rtrim(IndexFileName))-2)ORDER BY RIGHT( rtrim(IndexFileName), len(rtrim(IndexFileName))-2) |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-01-31 : 08:39:21
|
I don't know the table structure but it looks like there is no chance for the engine to take any advantage of the index because of your WHERE clause.This is caused by not storing splitted informations in own columns like Drive and Path and Filename and Extension.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Gratin
Starting Member
6 Posts |
Posted - 2012-02-01 : 04:13:04
|
Thanks very much for the advice. I've splitted the path in different fields as you recommended. Now it is really faster.Here is the Select statement now:SELECT indexFolder, indexFile, indexExt , max(case when rtrim(IndexDrive) = 'T:' then IndexFileLastWrite end) as T , max(case when rtrim(IndexDrive) = 'U:' then IndexFileLastWrite end) as U , max(case when rtrim(Indexdrive) = 'V:' then IndexFileLastWrite end) as V FROM [tblIndex] WITH (INDEX(PK_IndexFileName))WHERE ((@booFile = 0 AND (IndexFile = '' or IndexFile is null)) OR (@booFile = 1 AND IndexFile <> '' )) and (indexFileName like '%' + @txtSearch + '%') and indexLevel = @intLevel GROUP BY indexFolder, IndexFile, indexExt ORDER BY indexFolder, IndexFile, indexExt |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-01 : 12:38:22
|
| "WITH (INDEX(PK_IndexFileName))"Is the Optimiser not automatically picking the most efficient index? (I presume your indexes are recently rebuilt, and all statistics are recently updated? ) |
|
|
|
|
|
|
|
|
Optimization