Delay in executing a query from VB

Question

Delay in executing a query from VB

sajykms

Newbie

Points: 2
More actions
December 9, 2007 at 5:14 am

#181956

The query executes very fast in Query Analyser while it takes almost 5 minutes in executing form VB

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004704 More actions · Answer 1

That's probably because when you execute it from VB, you're doing it as embedded SQL... which has no chance of reusing an Execution Plan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 741285 More actions · Answer 2

I have seen this before and it is frustrating. Try a stored procedure and see if you can get it to behave better. Also, are you executing the same query, meaning same WHERE clause or parameters?

sajykms Newbie Points: 2 More actions · Answer 3

The problem was solved when i changed connection from DSN to oledbProvider.

Steve Jones - SSC Editor SSC Guru Points: 741285 More actions · Answer 4

Very interesting. If you change it back, does it slow down again? Be interesting to know. Sounds like some kind of bug, but not sure it's worth chasing down.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Depending on the syntax - an oleDB provider can execute the query client-side (or locally). That means you might be pulling a LOT of data only to have it excluded by the client execution of the WHERE clause.

Again - put it in a stored procedure, or if relevant - look at using something forcing remote or server-level execution, like the OPENQUERY syntax.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?