473,322 Members | 1,494 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Can I speed up this script...help?

Ok,
This script is something I wrote for bringing up a report in reporting
services and it is really slow...Is their any problems with it or is
their better syntax to speed it up and still provide the same report
results?:

SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type,
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
FROM tblTravelDetail t1 INNER JOIN
tblTravelDetailAmount t2 ON t1.TravelDetailId =
t2.TravelDetailId INNER JOIN
tblTravelDetail t3 ON t2.TravelDetailId =
t3.TravelDetailId INNER JOIN
tblTravelDetailMember t4 ON t3.TravelDetailId =
t4.TravelDetailId INNER JOIN
tblTravelEvent t5 ON t1.TravelEventId =
t5.TravelEventId INNER JOIN
amount_type t6 ON t2.amountTypeId =
t6.amount_type_id INNER JOIN
period t8 ON t1.PeriodID = t8.period_id
WHERE (t1.MemberId = @MemberId) AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type

Any help is appreciated.
Thanks,
Trint

Sep 27 '05 #1
4 1622
Stu
There's no way for us to answer that without seeing your execution
plan. What are your indexes like?

Stu

Sep 27 '05 #2
On 27 Sep 2005 04:51:43 -0700, trint wrote:
Ok,
This script is something I wrote for bringing up a report in reporting
services and it is really slow...Is their any problems with it or is
their better syntax to speed it up and still provide the same report
results?:

SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type,
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
FROM tblTravelDetail t1 INNER JOIN
tblTravelDetailAmount t2 ON t1.TravelDetailId =
t2.TravelDetailId INNER JOIN
tblTravelDetail t3 ON t2.TravelDetailId =
t3.TravelDetailId INNER JOIN
tblTravelDetailMember t4 ON t3.TravelDetailId =
t4.TravelDetailId INNER JOIN
tblTravelEvent t5 ON t1.TravelEventId =
t5.TravelEventId INNER JOIN
amount_type t6 ON t2.amountTypeId =
t6.amount_type_id INNER JOIN
period t8 ON t1.PeriodID = t8.period_id
WHERE (t1.MemberId = @MemberId) AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type

Any help is appreciated.
Thanks,
Trint


Hi Trint,

My first observation is that the join to the second instance of table
tblTravelDetail (the one aliased as t3) is redundant - it will simply
provide yet another copy of each row in the first instance of the same
table (aliased as t1). Remove this join and change all references to the
alias t3 to t1.

Next, I note that the tables tblTravelDetailMember and tblTravelEvent
(aliased as t4 and t5) are not used in the query at all. You can quite
probably remove these two tables from the FROM clause and still get the
same results.

If, after these modifications, the query still runs too slow, than
please provide more information. The minimum information required is:
- Complete information about your design: all tables (as CREATE TABLE
statements, including all constraints and properties), all indexes (as
CREATE INDEX statements, unless the index is created as a side effect of
a constraint), and some information about the estimated number of rows
in each table;
- The exact query that you ran (i.e. after making the modifications I
suggested above);
- The actual execution plan (use SET SHOWPLAN TEXT ON to get this
information in a format that you can copy in the newsgroups);
- The time the query took, and the time you think it ought to take.

The following extra information is also very usefull:
- Some rows of sample data that help to illustrate what the query should
achieve (posted as INSERT statements);
- The expected output of the query for those statements (not needed if
the query in your messages produces that resutls);
- A short description of the business problem that you're trying to
solve with this query.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Sep 27 '05 #3
Trint,

It looks pretty good so far. Your indexes are very important, since the
WHERE clause is probably not very restrictive, and you have many joins
(some of which may be unnecessary). So in addition to Hugo's advice the
following tips:

1) Make sure you have proper indexes in place. You could consider using
the Index Tuning Wizard, or you could add many indexes yourself, check
the execution plan, and remove all unused indexes. When doing that, you
could also consider covering indexes.

For example, you could create the following (temporary) indexes for
table tblTravelDetailAmount :
- tblTravelDetailAmount (TravelDetailId, amountTypeId)
- tblTravelDetailAmount (TravelDetailId, amountTypeId, amount)
- tblTravelDetailAmount (amountTypeId, TravelDetailId, amount)
If there is a clustered index on TravelDetailID, then you could add the
following (temporary) indexes:
- tblTravelDetailAmount (amountTypeId)
- tblTravelDetailAmount (amountTypeId, amount)

2) Remove the t2.amount <> 0 predicate from the query, and check if this
makes the query run faster or slower

3) Table amount_type seems to be a lookup table. If it is, then make
sure that in addition to the Primary Key (on amount_type_id), the table
also has a Unique constraint (or index) on the description column (on
amount_type). If the query does not benefit from this, then you could
consider rewriting the query to this:

SELECT t1.MemberId, t1.PeriodID, t8.start_date,
t6.amount_type_id, MIN(t6.amount_type) AS amount_type, ...
// added MIN() on this line
FROM tblTravelDetail t1 ...
WHERE (t1.MemberId = @MemberId)
GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id
// removed amount_type from this line

Hope this helps,
Gert-Jan
trint wrote:

Ok,
This script is something I wrote for bringing up a report in reporting
services and it is really slow...Is their any problems with it or is
their better syntax to speed it up and still provide the same report
results?:

SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type,
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
FROM tblTravelDetail t1 INNER JOIN
tblTravelDetailAmount t2 ON t1.TravelDetailId =
t2.TravelDetailId INNER JOIN
tblTravelDetail t3 ON t2.TravelDetailId =
t3.TravelDetailId INNER JOIN
tblTravelDetailMember t4 ON t3.TravelDetailId =
t4.TravelDetailId INNER JOIN
tblTravelEvent t5 ON t1.TravelEventId =
t5.TravelEventId INNER JOIN
amount_type t6 ON t2.amountTypeId =
t6.amount_type_id INNER JOIN
period t8 ON t1.PeriodID = t8.period_id
WHERE (t1.MemberId = @MemberId) AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type

Any help is appreciated.
Thanks,
Trint

Sep 27 '05 #4
Thank you for your responses...Adding indexes to the tables made the
process faster.
Trint

Sep 28 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: Bulba! | last post by:
One of the posters inspired me to do profiling on my newbie script (pasted below). After measurements I have found that the speed of Python, at least in the area where my script works, is...
1
by: Perttu Pulkkinen | last post by:
Has someone for leisure's sake develepoded a script that would help to set quite same marquee speed in different environments. Why: because I noticed a VERY big difference in certain Mac's in local...
23
by: Mark Dickinson | last post by:
I have a simple 192-line Python script that begins with the line: dummy0 = 47 The script runs in less than 2.5 seconds. The variable dummy0 is never referenced again, directly or indirectly,...
12
by: PD | last post by:
I am currently making a dating website. I want to have some information on how to structure the database and the php files so that I can achieve speed and efficiency. Can some one please give...
6
by: lawrence k | last post by:
The following function is way too slow. If anyone has any suggestions about how to speed it up, I'd be grateful for them. We have to call this function 36 times on one page, and I think each time...
6
by: CptDondo | last post by:
I've got a problem with performance... I'm working on a fairly slow embedded platform (a 200 MHz ARM board). We run a browser and a webserver on the same platform. I've timed the performance,...
8
by: SaltyBoat | last post by:
Needing to import and parse data from a large PDF file into an Access 2002 table: I start by converted the PDF file to a html file. Then I read this html text file, line by line, into a table...
4
by: wang frank | last post by:
Hi, While comparing the speed of octave and matlab, I decided to do a similar test for python and matlab. The result shows that python is slower than matlab by a factor of 5. It is not bad since...
11
by: kyosohma | last post by:
Hi, We use a script here at work that runs whenever someone logs into their machine that logs various bits of information to a database. One of those bits is the CPU's model and speed. While...
10
by: Devang | last post by:
Hello, I am using php script to upload file. some times if file size is too big(1GB) it takes too much time to upload. Can someone suggest me the way to increase upload speed. thanks
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.