Performance: RDO(VB5.0) vs. ISQL/w...
Chris
Hello again.
I'm writing because I've observed what I consider to be faulty behaviour:
If I do a query in VB5.0 using RDO, a "Select * from ..." with a result
of 76 records, it takes 20 seconds. No matter what. NT Performance
shows erratic activity for about 10 seconds, then a high plateau (~95%)
for the last half before quieting down. That's for a
"Set rdoRS = rdoCN.OpenResultset(strSQL, rdOpenForwardOnly,
rdConcurReadOnly, rdExecDirect)"
call with strSQL="Select * from <table> where srchname1='xxxxx'"
It's very queer to me that the same query executed in ISQL/w takes
less than 1 second. There's got to be overhead, but more than 20x?
What's a faster way to access a DB (and just how fast is VBSQL and
direct ODBC writing?)? Are there settings I can change to get better
performance? Or is there a better SQL statement I can make that
takes less time?
Another question I have is about "LIKE" in SQL statements. I'm seeing
a huge performance difference between "like 'ac%'" and "like 'acm%'"
on an indexed field - the first takes several seconds less than the second.
Is the "BETWEEN" operator a better tool if I want to be more specific
(eg. include more letters) than "LIKE"?
Hints about VB/RDO/ODBC/SQLS6.5 performance are greatly appreciated.
-Chris
Bob Pfeiff
You might want to run SQL Trace to see what's going to SQL Server when you
run the query from RDO.
--
Bob Pfeiff, MCSD
Spectrum Technology Group, Inc.
bo...@spectrumtech.com
<<remove "nospam" from e-mail address to reply>>
Chris
Thanks for the hint - I tried it, and I'm not sure what to
make of the results - they seem to say exactly what I
expressed in my last posting - that RDO is taking a
long time.
However, it does give me a better idea of what's
going on. It could (has to?) be that the sp_cursorfetch
is creating a cursor on SQL Server, then passing
that data back through ODBC/RDO to VB, but what
would take so much time? Could I get the
Performance monitor to tell me anything more than
that the time is being whittled away on that
rdoCN.openresultset call? Is there another utility I
should look at? Then again, is there an "RPC Trace"
utility, as that's where (from SQL Trace) I see the time
went.
I've included the copy from SQL Trace at the end - I
ran the query in my VB5.0 program (from within VB),
then ran it in ISQL/w. Continued ideas and suggestions
are appreciated! It seems especially strange that the
ISQL/w has no 'finishing' activity, similar to 'sp_cursorclose',
it just does the SQL statement. Is there a similar way I
could utilize rdo.connection.Execute? It's a sub, so I'd say
not, but is there other functionality - either a faster cursor,
or a way other than cursors to get at the DB - say, an RDO
that bypasses RPC?
-Chris
-----------------------------------
go
-- 2/20/98 12:46:48.780 New connection (ID=22, ...App='Visual Basic',
Host='TARDIS'(cc) )
-- 2/20/98 12:46:48.950 SQL (ID=22, ...App='Visual Basic',
Host='TARDIS'(cc) )
set quoted_identifier on use "FullBackup"
go
-- 2/20/98 12:46:48.990 SQL (ID=22, ...App='Visual Basic',
Host='TARDIS'(cc) )
set language us_english
go
-- 2/20/98 12:46:49.100 SQL (ID=22, ...App='Visual Basic',
Host='TARDIS'(cc) )
select ...
go
-- 2/20/98 12:46:49.270 RPC (ID=22, ...App='Visual Basic',
Host='TARDIS'(cc) )
sp_datatype_info 0
go
-- 2/20/98 12:46:49.440 RPC (ID=22, ...App='Visual Basic',
Host='TARDIS'(cc) )
sp_cursoropen NULL, "select * from SSCOMPYS WHERE srchnme1 = 'acme' ", 4,
1, NULL
go
-- 2/20/98 12:46:49.570 RPC (ID=22, ...App='Visual Basic',
Host='TARDIS'(cc) )
sp_cursorfetch 29121328, 2, 1, 100
go
-- 2/20/98 12:47:15.536 RPC (ID=22, ...App='Visual Basic',
Host='TARDIS'(cc) )
sp_cursorfetch 29121328, 2, 1, 100
go
-- 2/20/98 12:47:15.646 RPC (ID=22, ...App='Visual Basic',
Host='TARDIS'(cc) )
sp_cursorclose 29121328
go
-- 2/20/98 12:47:15.676 Disconnection (ID=22, ...App='Visual Basic',
Host='TARDIS'(cc) )
-- 2/20/98 12:47:18.600 SQL (ID=21, ...App='Microsoft ISQL/w',
Host='TARDIS'(11c) )
select suser_name()
go
-- 2/20/98 12:47:18.630 SQL (ID=21, ...App='Microsoft ISQL/w',
Host='TARDIS'(11c) )
select * from sscompys where srchnme1='acme'
Bob Pfeiff
Since the app is only doing two sp_cursorfetch calls, I'm not sure why it 's
taking 20 seconds, but you are using a server cursor. You will probably get
a faster response if you set the .CursorDriver of the rdoConnection object
to rdUseOdbc which will stream the data back to the client rather than build
a temporary or keyset table in SQL Server, then doing fetches to get the
results to the client. Results to isql/w are streamed directly back to the
query window (via db-library, not ODBC) which is why it is faster than the
ODBC server cursor you've got now.
Hope this helps.