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 |
|
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. |
 |
|
|
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. |
|
|
|
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. |
|
|
|
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) |
|
|
|
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. |
|
|
|
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.. |
|
|
|
|
|
|
|
|
CharIndex funtion running extremely slow