Help on Indexing

[Dylan Snyder]

I have an SQL table with 19000 records. everything running off of this table is very slow. If I go to the table and try to Control F find the last record using a clustered index field, the search takes about 45 seconds. Is this normal? the weird thing is that If I were to create a query and type in that record as the criteris, it would pop up instantly. Any suggestions?

 

[namliam]

A Control-F is passing thru all the records, not using the index at all... while the Query does use that index.

Not to strange a time difference IMHO.

 

[Dylan Snyder]

Thanks, that makes sense.

 

[namliam]

None....

Actually that depends... do they need to search many many many times? Then fetching the table one time and doing the CTRL-F thing is the best way to do it....
Maybe you want to make sure to add a "MoveLast" into your form open code so the users only have the wait on form open.

More likely is that you dont want a 1 minute wait on open or on search. Then you want to make a "search form" and alter your SQL to do the "flash query" in SQL Server to retrieve the record(s) you need quickly.

Do a search on "search form" in the reference forums you should find a couple of samples there on how to get started. Ask more detailed questions here if you get stuck.

Good Luck !

 

[Dylan Snyder]

Brilliant,
I did some reasearch and the search form looks like it is much more efficient(and it looks like building them could be some fun on a Tuesday Morning)+I won't have to bug IT. Thanks again!

 

[namliam]

Good luck, feel free to post back here when you have specific questions... or run into troubles...

 

[Dylan Snyder]

Thanks, my prototype search form does the job much more efficiently than the old way-of course-I may have opened Pandora's box because I will probably have to create search forms for every form in the database.

Anyway, Do you know why the control F method was not all that slow when the tables were local access tables but when I migrated the tables to SQL the Control F suddenly became very slow?

 

[namliam]

Because of the data traffic needed between the server and the database.

And be real... You really didnt think that CTRL-F stuff would hold up in the end right?? Bleg it is UGLY !

 

[MSAccessRookie]

I do not know if this will be helpful or not, but we had a similar problem here, and I discovered that I had Server views that were SchemaBound and had indexes that were opposite to the table.

In one case, the table was sorted by Invoice Number Ascending, and one of the queries accessed the data by Invoice Number Descending. The result was a delay of monumental proportions. Once it was fixed, the processing time dropped from over 35 seconds to just under 1.5