ASP.Net and SQL

Question

Hi,

I am developing an asp.net application where user inputs set of document number in excel sheet and my application should search for the particular records in the database and return the records which contains the value that was made as an input.

For example if the document number which was given as input is "2245678"

In database the field which contains the value will be of the format LibraryName:2245678:Version1, because of how is stored in the database i had to put an like query as

Select from table where documentnumber like '%2245678%'

This set of sql statement has to be repeated for the all the document numbers that are passed as input in the excel sheet, This indeed will cause performance problems ..

Is there any other way of doing it?

Answer 1

You don't specify your Database Provider, but Full Text Indexing on these fields may be what you need.

Note: I second JBKing's comment if it is appropriate for your application.

Answer 2

Since you cannot modify the schema, see if this performs any better:

Select * 
from table 
where substring (documentnumber , charindex(':', documentnumber ) + 1, charindex(':', documentnumber , charindex(':', documentnumber ) + 1) - charindex(':', documentnumber ) - 1) = '2245678'

Edit:

If there are a finite (small) number of library names, you may get better performance doing a hard-coded query:

Select * 
from table
where documentnumber like 'LibraryName1:' + '2245678' + ':%'
    or documentnumber like 'LibraryName2:' + '2245678' + ':%'
    or documentnumber like 'LibraryName3:' + '2245678' + ':%'

If the documentnumber field is actually indexed, this should perform pretty well.

Answer 3

Well if its stored like that and you can hand on heart say that what you have is

Field1:Field2:Field3 then you can use substring and charindex instead of like %%. I have no idea if it's performance is improved but I imagine it would be since the over head is less in locating the index of one char than it is to sequentially scan an entire string for you expression

So this is a close apporximation for you in SQL Server 2005

SELECT SUBSTRING(SUBSTRING('field1:field2:field3',CHARINDEX(':', 'field1:field2:field3') + 1, 999),1 ,CHARINDEX(':', SUBSTRING('field1:field2:field3',CHARINDEX(':', 'field1:field2:field3') + 1, 999))-1)

There's one way :)