When I run the sql below for the first time after opening dbsys it take 50+ seconds, the second time 12+ seconds and the subsequent ones
7+ seconds. why is this?
SELECT D.LocationNo, sum(D.SalesValue)as TotVal, D1.ECRDept  
From ProductDay D JOIN ProductDetail D1 on ( D1.ProductCode = D.ProductCode) and (D1.LocationNo = D.LocationNo)
WHERE D.SalesDate >= '2009-05-16' AND D.SalesDate <= '2009-05-22' And D.LocationNo = 1
group by d1.ECRDept
JOINOPTIMIZECOSTS

Plan results for 3rd run
================================================================================
SQL statement (Executed with 4.26 Build 3)
================================================================================

SELECT D.LocationNo, sum(D.SalesValue)as TotVal, D1.ECRDept  
From ProductDay D JOIN ProductDetail D1 on ( D1.ProductCode = D.ProductCode)
and (D1.LocationNo = D.LocationNo)
WHERE D.SalesDate >= '2009-05-16' AND D.SalesDate <= '2009-05-22' And
D.LocationNo = 1
group by d1.ECRDept
JOINOPTIMIZECOSTS

Tables Involved
---------------

ProductDay (D) table opened shared, has 1200265 rows
ProductDetail (D1) table opened shared, has 615222 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more grouped rows

Result set will be grouped by the following column(s) using a temporary index:

ECRDept

Result set will be ordered by the temporary index created for the grouping

WHERE Clause Execution
----------------------

The expression:

D.SalesDate >= '2009-05-16' AND D.SalesDate <= '2009-05-22' And D.LocationNo = 1

has been rewritten and is PARTIALLY-OPTIMIZED, covers 47174 rows or index keys,
costs 9109299 bytes, and will be applied to the ProductDay table (D) before any
joins

Join Ordering
-------------

The driver table is the ProductDay table (D)

The ProductDay table (D) is joined to the ProductDetail table (D1) with the
INNER JOIN expression:

D.ProductCode = D1.ProductCode and D.LocationNo = D1.LocationNo

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

Join Execution
--------------

Costs ARE being taken into account when executing this join
Remove the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force
the optimizer to stop considering costs when optimizing this join

The expression:

D.ProductCode = D1.ProductCode and D.LocationNo = D1.LocationNo

is PARTIALLY-OPTIMIZED and is estimated to cost 34570 bytes per candidate row

================================================================================
>>>>> 3 rows affected in 7.562 seconds
================================================================================



I am running this on a laptop with with 2Gb ram and core dual processor.
This SQL is part of a VB6 program using ODBC the dates and the LocationNo are passed as parameters. The dates entered are constant but
the LocationNo will change as the LocationNo changes as read from another file. there are 54 enteries in that file. The reason for my testing
the SQL in dbsys is that the VB6 program is taking 30+ minutes to complete. Any ideas will be greatly appreciated

Malcolm


>When I run the sql below for the first time after opening dbsys it take 50+ seconds, the second time 12+ seconds and the subsequent ones
>7+ seconds. why is this?

Almost certainly down to DBISAM / Windows caching with possibly a little bit for opening tables.

>I am running this on a laptop with with 2Gb ram and core dual processor.
>This SQL is part of a VB6 program using ODBC the dates and the LocationNo are passed as parameters. The dates entered are constant but
>the LocationNo will change as the LocationNo changes as read from another file. there are 54 enteries in that file. The reason for my testing
>the SQL in dbsys is that the VB6 program is taking 30+ minutes to complete. Any ideas will be greatly appreciated

WOW. Both sets of times (DBSys & VB6) from your PC ie not running over a network or anything ie you're positive its the same set of tables each time for starters?

Roy Lambert [Team Elevate]

WOW. Both sets of times (DBSys & VB6) from your PC ie not running over a network or anything ie you're positive its the same set of tables
each time for starters?

Absoutly posative Roy. I copy the data on a daily basis from the live system to my laptop so there is no chance of my programs corrupting
the live data. There is only one version of the data on my laptop. As you can see from the plan ProductDay is at 1.2 million records and
growing ProductDetail is at 615,000 and fairy stable.

Any suggestions
Malcolm

Malcolm

The varying times in DBSys can be ignored, that's almost certainly down to cache / buffering. The worrying thing is 30mins in VB6 and there I can't help at all. I asked about the tables because if they were coming over a very slow LAN that could contribute to it (not 30mins worth though).

The only thing I can think of is to try with your AV etc turned off just in case there's some interference there.

You might also want to email Tim direct rather than waiting for him to surface in the NGs

Roy Lambert [Team Elevate]

Hi Roy,

I have tried turning of the Antivirus and excluding the DAT files from the scanning process. This has reduced the time to around 20- 25
minutes. buts that all.

what's Tim's email

many thanks
Malcolm

"Malcolm" <Malcolm@jjwilsonltd.com> wrote in message
news:9AB02A17-2932-4E0F-9106-72B0229ADE6A@news.elevatesoft.com...
> When I run the sql below for the first time after opening dbsys it take
> 50+ seconds, the second time 12+ seconds and the subsequent ones
> 7+ seconds. why is this?

For subsequent runs the query is already prepared. That might make the
difference, or at least part of the difference.

> SELECT D.LocationNo, sum(D.SalesValue)as TotVal, D1.ECRDept
> From ProductDay D JOIN ProductDetail D1 on ( D1.ProductCode =
> D.ProductCode) and (D1.LocationNo = D.LocationNo)
> WHERE D.SalesDate >= '2009-05-16' AND D.SalesDate <= '2009-05-22' And
> D.LocationNo = 1
> group by d1.ECRDept
> JOINOPTIMIZECOSTS
>

The query is simple enough so you can explore running it with some changes
(such as eliminating the where clause) and see what happens. I would also
run a simple test pulling a field from each table (no aggregates, no group
by) to test the JOIN, there might be an issue with the indexes.

The monumental difference between ODBC and DBSYS is probably a Tim issue.

Robert

"Robert" wrote:

many thanks Robert,
I will "play" some and see what happens

Malcolm

Malcolm


timyoung at elevatesoft dot com

Roy Lambert

Hi Malcolm,

You have excluded dat files but how about idx files? Or better if you can
exclude whole app directory?

Regards,
Hüseyin A.

"Malcolm" <Malcolm@jjwilsonltd.com> skrev i en meddelelse
news:1B65CE00-5D13-4341-81F2-25DBB15BC01D@news.elevatesoft.com...
> Hi Roy,
>
> I have tried turning of the Antivirus and excluding the DAT files from the
> scanning process. This has reduced the time to around 20- 25
> minutes. buts that all.
>
> what's Tim's email
>
> many thanks
> Malcolm
>

Malcolm,

Per email:

Have you tried running the query on a machine with no anti-virus installed ?
Sometimes the caching in Windows can result in better performance on
subsequent runs of a query, but 50+ seconds down to 7 seconds is extreme and
indicates that something else may be affecting the performance of the
initial runs.  If you want me to test it here for you I can, but I'll need
you to email me the data or provide a link where I can download it.

Also, you should convert the >= <= condition into using BETWEEN instead.  It
is much, much faster, especially with large source tables.

Tim Young
Elevate Software
www.elevatesoft.com