View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000479 | Database Workbench v5 | General | public | 2012-10-19 09:20 | 2014-10-15 17:55 |
| Reporter | Gunnar Peters | Assigned To | Martijn Tonies | ||
| Priority | normal | Severity | major | Reproducibility | always |
| Status | closed | Resolution | fixed | ||
| Fixed in Version | 5.0.4 | ||||
| Summary | 0000479: Preparing SQL statement with BLOB result column takes extra time | ||||
| Description | I 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. select 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. | ||||
| Tags | No tags attached. | ||||
| related to | 0001077 | closed | Martijn Tonies | SQL statements in SQL Editor are prepared/execute twice because of query plan |
|
|
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. |
| 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 |
