Web Large Select Statements

mark_me

Hello, I have a question related to sql in asp.net. I have come across an asp.net page which contains upto 2000 ids in where in clause in sql select statement(and keeps on increasing). The statement works perfectly good but am wondering if there is any limitation with this approach(ie restriction on sql statement size ). I understand that instead of searching many rows based on ids in the where in clause, one can use a sub select statement ie consider 2 tables , products and parts, with M:N, so there would be Products_Parts table.. consider one wants to select all 1000s of products for 1 part... this can be done by getting all product ids from Products_Parts and then using wherein to get all products.. Another approach would be a join statement like "Select * from products join (select product_id as pid from products_parts where partid=1) t on t.pid=products.id" Please note that a simple join can also be used but this situation that i have cannot be solved easily with the simple join and requires either a wherein clause or a sub select. I have designed the queries and seems like the wherein clause statement performs better than join one.. but again, statement size is increasing and am not sure if there are limitations with such approach.. Please provide help

Abhishek Sur

Hey... Dont bother about the statement size. It can be of pages. What is the concern, is that how you handle such a long statement. Modify the statement to make some ranges and remove numbers between them. It would be a good solution if it matches your criteria.. The most important concern on search is if the table is properly indexed or not. Always index on the fields that come across to a where statement.. Hope this help you. :rose:

Abhishek Sur

My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB

**Don't forget to click "Good Answer" if you like to.

mark_me

Thanks Abhishek, Your answers have always been a good help. Thanks again. I have another small question ... i searched for it but wasnt' able to find any good help. I am also trying to develop a multi word search which can perform autocorrect... Can u please provide an idea on how this is done or may be forward an article... Thankyou again

Abhishek Sur

First of all thanks for your appreciation. Well, if you need to do autocorrect, you can use LINQ. Its easy and handy.. Otherwise you can use this : http://www.asp.net/AJAX/AjaxControlToolkit/Samples/AutoComplete/AutoComplete.aspx or http://www.dotnetjohn.com/articles.aspx?articleid=215 :thumbsup::thumbsup:

Abhishek Sur

My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB

**Don't forget to click "Good Answer" if you like to.

mark_me

thanks for replying. I have used the autocomplete ajax extender but by multiple word search i meant how would i implement a search so that it allows "Battery for laptop aspire 234" or may be "Battery Aspire 234". Again by autocorrect i meant that if somebody happens to type in Apsire(instead of aspire) how would i suggest aspire ...