View Issue Details

IDProjectCategoryView StatusLast Update
0000479Database Workbench v5Generalpublic2014-10-15 17:55
ReporterGunnar Peters Assigned ToMartijn Tonies  
Status closedResolutionfixed 
Fixed in Version5.0.4 
Summary0000479: Preparing SQL statement with BLOB result column takes extra time
DescriptionI am using your software and have got a question as to why it is taking such a long time to prepare my query (time takes 15 min to prepare and 7 min to execute).
The query consists of reasonably simple commands and 4 joins.
       cast(je.EventTimeStamp as date) as Date,
       sum(case when je.EventType_ID = 1 then 1 else 0 end) as 'Number of Trips',
      10 more sums like the one above
from JourneyEvent je with(NoLock)
     join Journey j with(NoLock) on j.JourneyID = je.Journey_ID
     join contract c with(NoLock) on c.contractid = j.contract_ID
     join policy p with(NoLock) on p.PolicyID = c.policy_ID
     join EventType et with(NoLock) on et.eventTypeID = je.EventType_ID where c.supplier_id = 1 group by cast(je.EventTimeStamp as date) order by cast(je.EventTimeStamp as date)

A different software (free license like QueryExpress) does un the 6 minutes which is similar to your processed time but what is really letting the speed down is the prepared.
TagsNo tags attached.


related to 0001077 closedMartijn Tonies SQL statements in SQL Editor are prepared/execute twice because of query plan 


Martijn Tonies

2014-09-30 11:07

administrator   ~0000308

Hello Gunnar,

This is a long standing issue, caused by a change in, I believe, Database Workbench 3.

The issue is two-fold and is particularly visible to you because of the complex query.

For starters, I noticed the Query Plan is always fetched, even though you're not asking for it (by going to the Plan tab at the bottom), which caused the query to be prepared by SQL Server an additional time.

This was easily solvable.

The second cause, is the grid itself, because this third party control wants to fetch all blobs all the time, this can get very very slow. To work around this, I'm adding a "blob indicator field", which simply says "blob is null" or "blob is not null", to show the the image for the blob field or <null>.

From your timings, I take it you have a blob field in your result (can you confirm?), as adding this extra hidden field when there's blobs, and preparing the dataset buffers for it, required a re-prepare of the statement, basically executing it once more.

I've been able to work around this with last issue, with some extra work, the upcoming release should execute/prepare a query only once.

Issue History

Date Modified Username Field Change
2012-10-19 09:20 Gunnar Peters New Issue
2012-10-19 10:15 Martijn Tonies Status new => acknowledged
2014-09-16 14:51 Martijn Tonies Project Database Workbench v4 => Database Workbench v5
2014-09-26 17:13 Martijn Tonies Assigned To => Martijn Tonies
2014-09-26 17:13 Martijn Tonies Status acknowledged => assigned
2014-09-30 11:07 Martijn Tonies Note Added: 0000308
2014-09-30 13:15 Martijn Tonies Status assigned => resolved
2014-09-30 13:15 Martijn Tonies Fixed in Version => 5.0.4
2014-09-30 13:15 Martijn Tonies Resolution open => fixed
2014-10-10 13:40 Martijn Tonies Summary Preparing code for ages => Preparing SQL statement with BLOB result column takes extra time
2014-10-10 13:41 Martijn Tonies Category Microsoft SQL Server Module => General
2014-10-10 13:42 Martijn Tonies Relationship added related to 0001077
2014-10-15 17:55 Martijn Tonies Status resolved => closed