SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 CharIndex funtion running extremely slow
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mnq
Starting Member

4 Posts

Posted - 04/25/2013 :  13:54:21  Show Profile  Reply with Quote


I have a stored procedure running on client machines that uses the charindex function. One of the clients machines it takes > 2 minutes to run, everyone else takes less than two seconds. Similar data, same application. Any ideas?

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 04/25/2013 :  21:41:52  Show Profile  Reply with Quote
Is each client machine connecting to the same server, or do they have their own instances of databases? Are all the client machines including the one where you are experiencing slowness identical?

In either case, I suspect it has more to do with the table/query/amount of data than charindex function itself.

If all clients are connecting to the server, it could be a network issue.

If each client has their own database, it can be anything from performance of the server to poor statistics.
Go to Top of Page

mnq
Starting Member

4 Posts

Posted - 04/26/2013 :  11:01:54  Show Profile  Reply with Quote
Each client has their own databases. The client machines are probably not identical but are identical on the things that matter. Operating system, Sql server version, service packs are exactly the same. Server statistics of the slow machine are more advanced then the older faster clients.

I've ruled out the amount of data because the client who has 10 times the amount of data is running faster. The slow one is a new client, very little data.

The reason I suspect it's the charindex function is because I replace the charindex function with an "in" statement and I get fast results. Essentially I'm using charindex instead of in because I'm passing comma delimited IDs from a vb.net application. And so charindex parses the comma delimited ids.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 04/26/2013 :  11:06:06  Show Profile  Reply with Quote
Compare the execution plans on the machine where it runs slow with one from a machine where it runs slower. (Press control-m in SSMS window and execute). The query plans would/should be different. They can be different due to a number of reasons - likely causes are outdated statistics, too much index fragmentation etc.

Also, if you are able to, post the query - people on the forum may be able to offer faster alternatives. When you use a function in the where clause, query optimizer won't be able to use any indexes that may be on the columns in the functions.
Go to Top of Page

mnq
Starting Member

4 Posts

Posted - 04/26/2013 :  11:52:21  Show Profile  Reply with Quote
@PARAM1 would be students 1 2 and 3 ids in this form "1,2,3"

SELECT * FROM

DEPT INNER JOIN
TEST ON DEPT.DEPTNO = TEST.DEPTNO INNER JOIN
STUDENT ON TEST.ACCNO = STUDENT.ACCNO INNER JOIN
INDTEST ON TEST.TESTNO = INDTEST.TESTNO INNER JOIN
TEACHER ON STUDENT.TEACHERNO = TEACHER.TEACHERNO LEFT OUTER JOIN
[FREETEXT] ON INDTEST.TESTNO = [FREETEXT].TESTNO

WHERE (TEST.RESULT <> N''.'' OR
TEST.RESULT IS NULL) AND (CHARINDEX(',' + CAST(STUDENT.ACCNO AS varchar(10)) + ',', ',' + @Param1 + ',') > 0)
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 04/26/2013 :  12:12:42  Show Profile  Reply with Quote
As James mentioned, it could be a statistical thing. It might also be a parameter-sniffing or plan re-use issue.

I'd suggest you fix the query to help make your predicate more sargable.
1. If you can, use tabled-valued parameters.
2. If you can't do that, then I'd suggest you split/parse the incoming delimited list into a temp table/table variable and join to it.

Edited by - Lamprey on 04/26/2013 12:13:24
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 04/26/2013 :  12:45:58  Show Profile  Reply with Quote
A thrid solution that I forgot to mention is using dynamic SQL..
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000