Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Access viewing SQL-Server dreadfully slow

    Using the command EXEC ket.TRACK_EXPORT_sp_oversigt I reference a stored procedure from Access 2003.

    Running the procedure from query analyzer takes 6 minutes, while Access simply stalls totally and doesn't return anything even after 25 minutes.

    Why is this so? I mean, the procedure runs directly on the server, and returns a lot of rows. But whether I call it from Access or QA shouldn't make a difference, as the recordset itself is returned in the same fashion.... or??

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    how, exactly, are you issuing
    command EXEC ket.TRACK_EXPORT_sp_oversigt
    ?? izy
    currently using SS 2008R2

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    I do a pass-through query via ODBC

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    then it should be +/- the same delay as QA (esp. if your A connection is already open)

    haven't seen this sort of behaviour before. bizarre!

    anyhow - 6 minutes in QA is already a serious query! where did you find users that patient?
    are timeouts set to suit your long query?

    are you using DSN or hard-coded-connection-string saved in the pass-thru?
    hard coded is faster, but only fractions of a second, not x 4 or more

    did you check "manually" opening the pass-thru? still slow?
    ...or is it only slow opening the pass thru in your code to feed a rst or whatever?
    if only in code - which code?

    how did you build the pass thru: is it a static saved pass thru? or built from code?
    if code - which code?

    sorry - no inspiration for a fix yet.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Oct 2005
    Posts
    183
    These ARE monster queries... I work in the record industry, and the particular query I run finds a series of tracks based on a periode of time /typically a quarter of year).... each track has info to it, and subinfo about artists and instruments.. etc. etc... these are track informations from across the world, so we're not talking 10 reocrds ;-)

    6 minutes is peanuts... they used to rely on access db querying for this... YAAAAAAAAAAAAWN... so they hired me to redo a lot of it into sql-server.. and like I said, doing QA does the same stuff in 6 minutes. but for some reason the Access query vomits.

    Its a hardcoded readonly string, only thing it should do is hurl out the date, that will be dumped into an xls file through a small procedure that I will develop later. But for some reason it just stalls... I can't figure out why, but I've had this problem before.... I'm having doubts if the access query will do this through DAO, which might explain the problem, so I might make an attempt an a ADO output, which is usually much much faster when its readonly.

    Cheers, trin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •