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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 CharIndex funtion running extremely slow

Author  Topic 

mnq
Starting Member

4 Posts

Posted - 2013-04-25 : 13:54:21


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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-25 : 21:41:52
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 - 2013-04-26 : 11:01:54
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-26 : 11:06:06
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 - 2013-04-26 : 11:52:21
@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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-26 : 12:12:42
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.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-26 : 12:45:58
A thrid solution that I forgot to mention is using dynamic SQL..
Go to Top of Page
   

- Advertisement -