ASP.Net and SQL

Question

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?

Why would your database have fields formatted like that? Why not have a Library Name and Version as columns instead wrapped in a string? — JB King
– JB King, Commented Sep 10, 2009 at 14:44
Database was designed by the vendor and i dont have any control to change them — user171476
– user171476, Commented Sep 10, 2009 at 14:56
either use fulltext search, or extract the ID into a separate field. The way it is now, you won't be able to formulate any queries that can use indices - you'll always do full table scans for finding each and every entry..... not very optimal! — marc_s
– marc_s, Commented Sep 10, 2009 at 15:08
sigh In that case, don't forget to make sure your App does some validation of the input so you don't wind up with a SQL injection attack as that along with the potential for users to change the query to be rather free-formed is another concern that comes to mind. — JB King
– JB King, Commented Sep 10, 2009 at 15:35

Matthew Vines · Accepted Answer · 2009-09-10 15:38:41Z

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.

edited Sep 10, 2009 at 15:38

answered Sep 10, 2009 at 14:44

Matthew Vines

27.7k7 gold badges80 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matthew Vines Over a year ago

Well, by full text indexing the field you are basically trading resources up front (creating the index, and keeping it up) for query time performance (because all the text in the column is indexed). There are a lot of pitfalls and nuances to full text indexing, most dealing with handling the strain of keeping the index up to date, and I am far from an expert. But there are several articles out there to help you if you decide to go this route.

Robert · Accepted Answer · 2009-09-10 15:16:11Z

0

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 :)

answered Sep 10, 2009 at 15:16

Robert

1,8454 gold badges25 silver badges30 bronze badges

Comments

D'Arcy Rittich · Accepted Answer · 2009-09-10 15:17:56Z

0

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.

edited Sep 10, 2009 at 15:17

answered Sep 10, 2009 at 15:01

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

1 Comment

marc_s Over a year ago

probably not - in this case, you're using function on your WHERE clause, too - so no indices will be used

Collectives™ on Stack Overflow

ASP.Net and SQL

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related