Dear all, I hope one of you will

[ThierryF]

Dear all,

I hope one of you will be able to help me out on that one!

One of our client keeps on getting a timeout error when querying its database via our product.

This query is timing out using ADO 2.5 in visual basic 6.0 sp5. The query is forwardonly, readonly which should make it fearly fast. We also use getrows, which may be at the cause of the problem, but we are not sure as we have clients with similar database that have 3,000,000 records and do not have that problem?

He is searching for an document no, document type and a date.

If he is searching by document no, immediate respond and he gets the information back. This number is unique and is indexed.

If he is searching by document type (which obviously would have a lot of duplicates!), with document no, he will get a time out error. Same for the date.

Both Document Type and Date are also indexed?

Document Type seems to be the biggest problem. It is a nvarchar, size 50.

There is only about 800,000 records.

Any ideas on what I can do to get rid of the time out would be great i.e. vb, ado, sql server?? anything advice or tips will be greatly appriciated!

Regards,

Thierry

 

[fluteplr]

Have you updated the index statistics lately? Have you tried running the same query through query analyser on their server to see where the bottleneck is?

 

[tlbroadbent]

Is the query using = or LIKE in the criteria. If you use LIKE, SQL will scan the table rather than use an index. The query will then be slower. You'll need to set the query timeout value higher in the client application.

Try running the queries in Query Analyzer and show the estimated query plan (SQL 7 and higher). That should give you an idea where the bottleneck exists. You can also use the Index Tuning Wizard which may show additional indexes that would help with query performance. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[ThierryF]

Thanks for getting back so quickly.

This is going to sound really bad,

but how do I update the index statistics??

I will give it a shot with the query analyser now and see if I can see anything dodgy??

Thierry

 

[tlbroadbent]

Using SEM, you can set Auto Create Statistics on the database so SQL will keep the statistics updated. You can also run UPDATE STATISTICS on the table.

Example: UPDATE STATISTICS authors Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[ThierryF]

Thanks both very much!

As far as I know, unless we detect % in the textbox, we use the = sign in the query, so it should be using the indexes!

I will check the code to see if we set the timeout value in ado for that specific query, but I don't think so.

What should I be looking for in the query analyser in terms of information coming back to me. All our fields are indexed as the user may required to search by any combinations of the fields.

I have ran the Tuning Wizard, and it is telling me that all is tuned OK!!!

Any other ideas?

Thanks very much!