c# - SQL - speed up code to DB

I have a page with 26 sections - one for each letter of the alphab开发者_StackOverflow社区et. I'm retrieving a list of manufacturers from the database, and for each one, creating a link - using a different field in the Database. So currently, I leave the connection open, then do a new SELECT by each letter, WHERE the Name LIKE that letter. It's very slow, though.

What's a better way to do this?

TIA

Since you are going to fetch them all anyway, you might find it faster to fetch them in one go and split them into letter-groups in the code.

Looking at it from the other end, why do you need to fetch all the lists just to build a set of links? Shouldn't you fetch a single letter when its link is clicked?

It sounds like you are doing up to 26 queries, which will never be fast. Often a single db query can take at least 40 ms, due to network latency, establishing connection, etc. So, doing this 26 times means that it will take around 40 x 26 ms, or more than one second. Of course, it can take much longer depending on your schema, data set, hardware, etc., but this is a rule of thumb that gives you a rough idea of the impact of queries on overall page render time.

One way I deal with this kind of situation is to use a DataTable. Fetch all the records into the DataTable, and then you can iterate through the alphabet, and use the Select method to filter.

DataTable myData = GetMyData();
foreach(string letter in lettersOfTheAlphabet)
{
    myData.Filter(String.Format("Name like '{0}%'", letter));
    //create your link here
}

Depending on your model layer you may wish to filter in a different way, but this is the basic idea that should improve the performance a lot.

Assuming you are querying to determine which letters are used, so that you know which links to render, you could actually just query for the letters themselves, like this:

select distinct substring(ManufacturerName, 1, 1) as FirstCharacter
from MyTable
order by 1

get one result set from one query and split that up. There is quite a lot of overhead going out the the database 26 times to do basically the same work!

You could probably do it smarter with a stored procedure. Let the SP return all the information you need in one call, and suddenly you only have one database interaction instead of 26...

Bring back all the items in one set (dataset, etc..), either through stored procedure or query, including the field left(col1,1), and sorting by that field..

select left(col1,1) as LetterGroup, col1, url_column from table1 order by left(col1,1)

Then look through the whole resultset, changing sections when the letter changes.

• First letter in the alphabet sucks (sorry) as discriminator. You do not neet to split them actually (you could just ask for "where name like 'a%'), but whatever you run for that gives you on average a 1/26 or so split of the names. Not extremely efficient.

• What do you mean with "creating a link - using a different field in the Database" - this sounds like a bad design to me.

there are a couple ways u can do this. 1) create a view in your db that has all the manufactures and their website link and then continue to hit the view for each letter. 2) select all the manufactures once and store it in a .net dataset and then use that dataset to populate your links.

This seems dirty to me, but you could create a first letter CHAR column and trigger to populate it. Have the first letter from the manufacturer name stored in that column and index it. Then select * from table where FirstLetter = 'A'.

Or create a lookup table with rows A - Z and set up foreign key in the manufacturer table. Again you would probably need a trigger to update this information. Then you could inner join the lookup table to the manufacturer table.

Then instead of putting 26 datasets in the page, have a list of links (A-Z) which select and show each dataset one at a time.

If I read you right, you're making a query for every manufacturer to get the "different field" you need to construct the link. If so, that's your problem, not the 26 alphabetic queries (though that's no help).

In a case like that, the faster way is this one query:

SELECT manufacturer_name, manufacturer_id, different_field 
FROM manufacturers m
INNER JOIN different_field_table d
ON m.manufacturer_id = d.manufacturer_id
ORDER BY manufacturer_name

In your server code, loop through the records as usual. If you want, emit a heading when the first letter of the manufacturer_name changes.

For additional speed:

• Put that in a stored procedure.
• Index different_field_table on manufacturer_id.