Re: Parallel Connections
- From: "Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom>
- Date: Wed, 21 Dec 2005 18:11:41 -0000
>> Hi All,
>> I am using VB6 & ADO 2.5 to get data from SQL Server.
>> There are some queries that I run daily on our servers(using connections
and
>> recordsets in a single form in a VB project).
>> A lot of these queries take a time(more than one minute).
You might want to see if you can optimise the queries so they take less than
minute.
You might be using non-optimal ADO to get your data. Points on this:
1) Make sure all variables are strongly typed
DIM R As RecordSet
1) Use the least expensive Recordset you need. Dont ask for more
functionality than you require.
Not modifying the data? => Then make sure it is adLockReadOnly
Just needeo do MoveNext() ? => Then adForwardOnly for CursorType
Warning: Don't call MoveFirst() as it may cause a requery of the data _just_
to reposition the cursor.
If BOF is not true after Recordset Open() => you are already on first record
and that is so for SQL Server.
2) The fastest cursor out is the Server-sided, adForwardOnly, ReadOnly.
But you will certainly need to set CacheSize to something other than 1 to
realise performance.
I usually use powers of 2 like 64,128, 256
3) Collect method is fastest for getting value of data. Semi-documented.
4) Make _SURE_ you specify all parameters to Recordset Open.
VB Programmers frequently default and then wonder after the Open() why the
Recordset they get back does not has the functionality they want. It also
slows ADO down when it has to guess what the Soure object is.
5) If you find yourself repeatedly doing the same query but for different
parameters, set up a Command Object with parameters and an adhoc query. Just
keep changing the parameters to get Recordsets out.
Keep the Command Object alive for as long as you have queries.
6) Consider upgrading MDAC to 2.6 or better as that supports Record objects.
They are about 15% faster for singleton SELECTs than Recordsets.
7) Check with Query Analyser whether you have the right indexs on tables.
Make sure you use a WHERE clause to cut down the rows returned
And don't do SELECT * if all you are interested in is a few fields from the
table. Name the fields, every time. Why SELECT fields you are not interested
in? It takes time to transport these fields over a Network. RTRIM() fixed
length char fields as Network bottleneck will be slower than SQL Server.
There is a lot of mileage in all this, so I would check carefully whether
your ADO code & SQL queries & database are non-optimal first. Only if you
know they are optimal and are still looking for speed improvements should
you look for something else.
See also
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/improvperf.asp
and also Bill Vaughn's articles where much of what I say is culled.
>> So,my problem here is that I wait all the queries to be executed
separately
>> (One by One).
>> My question is:
>> Are there a way to run many queries on many servers parallel at the same
>> time on the same VB form
VB6 does not support threads.
If it did you could run different queries inside different threads.
Your only option is do multiple asynchronous and watch for event completion
One warning:
You cannot have more than 1 active ForwardOnly cursor per Connection with
SQL Server. Other cursor types are okay.
Stephen Howe
.
- Prev by Date: Re: Parallel Connections
- Next by Date: Re: RowChanging events in C# using partial classes (Easy in VB - Not C
- Previous by thread: Re: Parallel Connections
- Next by thread: Re: How to automaticly retrieve a new record(new line)
- Index(es):
Relevant Pages
|
|
