May 31, 2002 at 1:32 pm
I wonder if anyone can help me on this.
One of our applications has a web front end. One of the site pages reads some information from two tables and gets back to the user with some data regarding a specific creditor.
The SQL Statement in the asp page calls a SQL statement, which reads as follows:
SELECT CREDITOR_NAM_TAB.CRED_NAME AS[Creditor Name], COUNT(CREDITOR_NAM_TAB.CRED_NO) AS Total
FROM CLAIM_TAB INNER JOIN CREDITOR_NAM_TAB ON CLAIM_TAB.TR_CODE = CREDITOR_NAM_TAB.TR_CODE AND CLAIM_TAB.CRED_NO = CREDITOR_NAM_TAB.CRED_NO
WHERE (CREDITOR_NAM_TAB.CRED_NAME LIKE '% "Creditor_name" %')
GROUP BY CREDITOR_NAM_TAB.CRED_NAME
ORDER BY 'Total' DESC
If I run it against the table, the statement times out. Running query Analyzer it takes 1 minute and a couple of seconds to get the data back to me.
The variable entered by the user is "creditor_name." For example All the creditors where the creditor’s name is like Ford Motor Company (Ford has many subsidiaries with different names, but always with Ford in the title)
I tried to create a view to easy things out, but the view times out also. I thought a store proc could be the solution, but so far I haven’t have any luck. Could any assist? Any help is very welcome.
May 31, 2002 at 1:44 pm
Sorry. I forgot to mention that, the sql statement was working fine until the table got over 5 million (before the taable use to store around 1.5 to 1.7 million records) and the find option for such a generic variable got to complex for the statement to be read against the table. Thank you.
May 31, 2002 at 2:51 pm
Leading wildcards can often cause a table scan since indexes arent useful. You can look at whether you really need that functionality, or maybe provide it as a non-standard option, or consider using a full text index which should be more efficient for this type of operation.
Andy
May 31, 2002 at 2:55 pm
Thanks Andy. I was reading about Full text index right at this moment. I will check this out to see if this work better for this.
If you or anyone comes with more suggestions, I will be with my ears as wide as I can to listen and learn
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply