Thread: Weird Performance Issue with .NET

Weird Performance Issue with .NET

From
Greenwich Support
Date:
Hi All,

I've imported a VB6 project into VB.NET, and I'm quite appalled at the
database performance through ADO.NET which seems to be a factor of 3 slower.
In general this isn't too much of a problem, but there is one view which is
exhibiting bizarre behaviour:

CREATE VIEW vw_checklist AS

SELECT ch.order_id, ch.checklist_id, ch.called, ch.notes, ch.emailed,
cl.short_name, t.*
FROM tblchecklist ch
INNER JOIN tbltrader t ON ch.trader_id = t.trader_id
INNER JOIN tblclient cl ON t.client_id = cl.client_id
ORDER BY  cl.short_name, t.first_name, t.last_name

Now if you do a select on this view:
e.g. SELECT * FROM vw_checklist WHERE order_id = 1260

this query takes about 0.01 seconds under VB6, but under VB.NET it takes 1.2
seconds. The bizarre thing is if you run the SQL that creates the view, so
that it becomes:

SELECT ch.order_id, ch.checklist_id, ch.called, ch.notes, ch.emailed,
cl.short_name, t.*
FROM tblchecklist ch
INNER JOIN tbltrader t ON ch.trader_id = t.trader_id
INNER JOIN tblclient cl ON t.client_id = cl.client_id
WHERE order_id = 1260
ORDER BY  cl.short_name, t.first_name, t.last_name

the query takes 0.41 seconds in VB.NET, and 0.03 seconds in VB6 -- the VB6
application shows an *INCREASE* in the query execution time which makes
sense, as using the view makes for a more efficient query, but the VB.NET
application result does not make sense to me..........though having said
that, I'm a VB6 veteran, and a .NET newbie!

Still, weird behaviour though! Can anyone explain what is going on??!?!
TIA
simon



Regulated by the FSA
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have
receivedthis e-mail in error) please notify the sender immediately and destroy this e-mail. Opinions, conclusions and
otherinformation in this message that do not relate to the official business of Greenwich Europe Limited shall be
understoodas neither given nor endorsed by it. Prices quoted above are not legally binding until confirmed on the
telephone.No responsibility will be accepted for any action taken on these prices prior to telephoning us for
confirmation.Any unauthorized copying disclosure or distribution or the material in this e-mail is strictly forbidden.  
________________________________________________________________________
This email has been scanned for all known viruses by Greenwich Europe Limited powered by MessageLabs SkyScan service.
Formore information on a proactive anti-virus service working around the clock, around the globe, visit
http://www.messagelabs.com