list by first letter performance help?

[anorakgirl]

Hi,

I have a table with just under 50,000 records in it.

I've done an interface for browsing alphabetically, which basically builds the query:

select * from mytable where myfield like 'A%'

But its a bit slow (not surprisingly with that many records!)

I'm trying to speed it up a bit. I tried adding an index to myfield, but it made no difference. I guess that might not be used as it is a 'like' query.

I tried

select * from mytable where Left(myfield,1) ='A'

But that is just as slow. I wanted to create an index on 'Left(myfield,1)' as I thought that might help, but it seems you can't do indexes using functions (not even sure if you can on other databases, must have seen it somewhere to get the idea).

So, I just wondered if anyone has done this before and has any tips for improving speed?

Thanks!

~

http://www.anorakgirl.co.uk

~

 

[dbomrrsm]

create a filed in the table that holds left(myfiled,1) index that then use that to do the select

Update mytable
SET mynewfield = left(myfiled,1) 

--create the index on mynewfield 

select * from mytable where mynewfield = 'A'

should be pretty quick

[blue]DBomrrsm[/blue]

 

[donutman]

There's something wrong with your server if that query is perceptible slow with only 50,000 rows. The query should return a result with a blink of your eye. You may want to check for sufficient ram.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[anorakgirl]

it takes about 12 seconds both on my laptop and the production server (it is a bit more complicated than that though, with a couple of joins and other where clauses). should it be faster than that then? i'm not used to MS SQL server, usually use PostgreSQL or MySQL.

donutman, thanks. I might try that - I'd just need to do something so that when a row is updated the firstletter field is also updated. Can you have "triggers" on MS SQL tables?

thanks for suggestions!



~

http://www.anorakgirl.co.uk

~

 

[dbomrrsm]

In EM in table design select the new field and you can enter a formula in a box in the bottom left hand corner so this field is maintained automatically.

in this case the formula would be left(myfield,1) this would maintain yournewfield with the first letter of myfield.

[blue]DBomrrsm[/blue]

 

[donutman]

On my server with a table of 200,000 rows, it executes that simplified query in 6 sec. with a 2yr old single proc server with 1gig ram using SQLServer 2k.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Oh, just checked from my office (via VNC which is similar to pcanywhere but freeware) instead of at home (with a 1.2 mbit lan connection to server) and it returns the recordset in 1 sec.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

Yes MS SQL Server has triggers. SEe BOL for syntax. One thing to remeber in doing triggers is to always program it so that it can deal with multiple records itneh inserted and/or delted pseudotables.

I'm with Karl, a query like tha on such a tiny table should not be perceptibly slow. Perhaps the way you are connecting is the problem. Is it slow in Query Analyzer as well as when you run it from the app? What does the Execution Plan look like?


Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[anorakgirl]

hmm, time to have (yet another) word with the hosting company then... thanks

~

http://www.anorakgirl.co.uk

~

 

[donutman]

I'm guessing but I think here's your problem:

it is a bit more complicated than that though, with a couple of joins and other where clauses.

Show us the full query.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Oh, missed your last post while I was posting. You're not connected to server with an internal lan? Then it's your broadband connection!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[anorakgirl]

don't worry about the full query - i've tested the simplified version directly in the Query Analyser on my local computer (still 12 seconds).

And (its a coldfusion app) I've written a little page to test the simplified query on the server too (12 seconds execution time for the query as reported by cfquery tag).

I know my local computer isn't very high spec, I'll ask the hosts what the server is, to get a comparison with your results.

Thanks for the advice!




~

http://www.anorakgirl.co.uk

~