473,323 Members | 1,589 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,323 software developers and data experts.

optimising the following query

Dear All, Plz help me in optimising the following query,
Reduce repeatable reads from the table via select ,ythe table sare not
having referntial integrity constarints ,relations

CREATE proc Rolex136Sync
as
DECLARE @date varchar(50),@ydate varchar(50)
print CONVERT(char(11),(GETDATE()-1),100)
SET @date =
substring(CONVERT(char(11),(GETDATE()),100),5,2)+'-'+substring(CONVERT(char(11),(GETDATE()),100),1,3) +'-'+substring(CONVERT(char(11),(GETDATE()),100),8,4)
SET @ydate =
substring(CONVERT(char(11),(GETDATE()-1),100),5,2)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),8,4)
Print @date
Print @ydate
insert into
biiod.dbo.data_trans_currentday_test(MobileNo,UA,M essageID,ContentID,Description,MusicLabel,CPID,CPN ame,ContentType,Category,SubCategory,TransactionDa te,Units,Unitprice,Shortcode,Servicecode,OperatorI D,CatID,SubCatID,SpecialPackage,Royalties,
Operator,Circle,OPGPName)
(select mobileno,
(SELECT CASE ua
when 'unknown' then null
else ua
end) as ua,
(select case remarks
when 'unknown' then null
else remarks
end) as remarks,
contentid,
(select case description
when 'unknown' then null
else description
end) as description,
(select musiclabel from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as musiclable,
(select cpid from datalogs.dbo.contentprovider where cpname =
datalogs.dbo.translogs.cpname) as cpid,
cpname,
contenttype,
(select catname from datalogs.dbo.cont_Catg where catid in (select
catid from cont_master where contentid =
datalogs.dbo.translogs.contentid)) as category,
(select subcatname from datalogs.dbo.cont_subCatg where subcatid in
(select subcatid from cont_master where contentid =
datalogs.dbo.translogs.contentid)) as subcategory,
transactiondate,1 as Units, price,
(select case servicename
when 'AIRTELIVE' then remarks
when 'ALCOMBOPACKREG' then remarks
when 'HINDI' then remarks
when 'NOKIAGAL' then remarks
when 'SUDOKU' then remarks
when 'SUDOKU_APP' then remarks
else NULL
end) as SHORTCODE,
servicename,
(select case servicename
when 'TSTTNEWS' THEN 600
when 'TSTTWAP' THEN 600
when 'TSTT_MMS' THEN 600
when 'AKTEL' THEN 300
when 'TELEMOVIL' THEN 700
when 'COMCEL' THEN 701
when 'QATAR2900' THEN 1
ELSE
(select operatorid from datalogs.dbo.operator where phoneseries =
substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))
end) as operatorid,
(select catid from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as catid,
(select subcatid from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as subcatid,
(select specialpackage from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as specialpackage,
(select Royalties from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as Royalties,
(select case servicename
when 'AKTEL' then 'Aktel'
when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
when 'TELEMOVIL' then 'TeleMovil'
when 'COMCEL' THEN 'COMCEL'
when 'TSTTNEWS' then 'TSTT'
when 'TSTTWAP' then 'TSTT'
when 'TSTT_MMS' then 'TSTT'
when 'ALCLICKWIN6464' then 'Airtel'
when 'ALMMSPORTAL' then 'Airtel'
when 'ALMMSSMSDWN' then 'Airtel'
when 'ALMYALBUM646' then 'Airtel'
when 'HINDU6397' then
substring(remarks,1,PATINDEX('%.6397.%',remarks)-1)
else
(select OPname from datalogs.dbo.operator where phoneseries =
substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))
end) as Operator,
(select case servicename
when 'AKTEL' then 'Bangladesh'
when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
when 'TELEMOVIL' then 'El Salvador'
when 'COMCEL' THEN 'Gautemala'
when 'TSTTNEWS' then 'Trinidad'
when 'TSTTWAP' then 'Trinidad'
when 'TSTT_MMS' then 'Trinidad'
when 'HINDU6397' then substring(remarks,PATINDEX('%.6397.%',remarks) +
6,len(remarks)-PATINDEX('%-%',remarks))
else
(select Circlename from datalogs.dbo.operator where phoneseries =
substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))
end) as Circle,
(select case servicename
when 'AKTEL' then 'Aktel'
when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
when 'TELEMOVIL' then 'TeleMovil'
when 'COMCEL' THEN 'COMCEL'
when 'TSTTNEWS' then 'TSTT'
when 'TSTTWAP' then 'TSTT'
when 'TSTT_MMS' then 'TSTT MMS'
when 'ALCLICKWIN6464' then 'Airtel Click Win 646'
when 'ALMMSPORTAL' then 'Airtel MMS'
when 'ALMMSSMSDWN' then 'Airtel MMS SMS'
when 'ALMYALBUM646' then 'Airtel My Album'
when 'HINDU6397' then 'Hindu 6397'
else
(select OPname from datalogs.dbo.operator where phoneseries =
substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))
end) as OPGPName
from datalogs.dbo.translogs where transactiondate >= @ydate and
transactiondate < @date and servicename in
('AIRTELMMS_SUB','ALMYALBUM646','HINDU6397','MTV', 'QATAR2900','SIFY'))
go

Jul 28 '06 #1
2 1782
Eckhart (n.********@gmail.com) writes:
Dear All, Plz help me in optimising the following query,
Reduce repeatable reads from the table via select ,ythe table sare not
having referntial integrity constarints ,relations
I'm sorry, but you cannot just post a 180-line query to the newsgroup and
hope that someone will cast a magic spell. To do proper tuning requires
full knowledge of table and index, and also indication of data distribution.

I can give some quick general recommendations.
from datalogs.dbo.translogs where transactiondate >= @ydate and
transactiondate < @date and servicename in
('AIRTELMMS_SUB','ALMYALBUM646','HINDU6397','MTV', 'QATAR2900','SIFY'))
For this query to perform well, the optimal would be a clusterd index
on (transctiondate, servicename) or (servicename, transactiodate). In which
order the columns should appear it's difficult to tell, because it
depends on which condition that have the best filter mechanism.

I would also recommend replacing @ydate with

dateadd(DAY, -1, @date)

Then the optimizer gets an idea of how long the interval is. Keep in mind
that the optimizer do not know the run-time value of variables.
(SELECT CASE ua
when 'unknown' then null
else ua
end) as ua,
I don't think it matters for performance, but using a sub-SELECT for this
adds additional noise. Better to simply write:

CASE ua WHEN 'unknown' THEN NULL ELSE ua END AS ua

Or even shorter:

nullif(ua, 'unknown') AS ua
(select musiclabel from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as musiclable,
Here, on, the other hand is a potential performance stealer. In my
experiences nested queries in the SELECT list gives bad performance.
Better is to join to datalogs.dbo.cont_master in the main FROM clause
of the query. You have quite a few of these, and rewriting them into
joins, can very well make your day.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 28 '06 #2
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

CREATE PROCEDURE Rolex136Sync
-- proc names are "<verb><object>" in ISO 11179
AS
DECLARE
@date VARCHAR(50), -- reserved word, non-temporal data type and
toooooooo long
@ydate VARCHAR (50) -- non-temporal data type and toooooooo long
PRINT CONVERT(CHARr(11), (GETDATE()-1), 100);
-- print is for diagnostics
-- CONVERT() is proprietary
--GETDATE() is proprietary and should be CURRENT_TIMESTAMP
-- 1950's style COBOL strings for dates !!
...
(SELECT CASE ua
WHEN 'unknown' THEN NULL ELSE ua END ) AS ua,
-- why did you put a SELECT in this?
-- did you use CASE instead if NULLIF()?

...
CASE servicename
WHEN 'aktel' THEN 'aktel'
WHEN 'qatar2900' THEN 'star multimedia 2900'
WHEN 'telemovil' THEN 'telemovil'
WHEN 'comcel' THEN 'comcel'
WHEN 'tsttnews' THEN 'tstt'
WHEN 'tsttwap' THEN 'tstt'
WHEN 'tstt_mms' THEN 'tstt mms'
WHEN 'alclickwin6464' THEN 'airtel click win 646'
WHEN 'almmsportal' THEN 'airtel mms'
WHEN 'almmssmsdwn' THEN 'airtel mms sms'
WHEN 'almyalbum646' THEN 'airtel my album'
WHEN 'hindu6397' THEN 'hindu 6397'
ELSE
(SELECT opname FROM datalogs.dbo.operator WHERE phoneseries ..)
END

-- why isn't this an auxiliary look-up table? Why only one operator in
that table?

Your code is basically 1950's COBOL written in SQL and is awful. You
are treating temporal data as insanely loooooonng strings and doing
display work in the database and not the application. You even use
PRINT in production code!

Singular table names is another COBOL thing. That language (and others
that are 40+ years old and non-relational) did record-at-a-time process
so you did bring one Operator into the program each loop. But SQL is
set-oriented and we bring many operators into the program without a
loop.

Jul 31 '06 #3

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

Similar topics

2
by: Jasper Bryant-Greene | last post by:
I have a database of movie titles, with about 78,000 records, and a database of related people (directors, writers, actors/actresses etc.) with about 141,000 records. I display a random movie out...
1
by: Howie Goodell | last post by:
Hello -- I am trying to optimise a JDBC connection and an Oracle 9i database for reading millions of records at a time to a 1 Gig PC running Win2K or XP. Two questions: 1. Does anyone have...
3
by: Andrew | last post by:
Hi, I have a problem I would really appreciate help with. I am generating dynamic SQL and need to optimise it. The specific example I am trying to optimise looks like this: SELECT DISTINCT...
4
by: Paul Brown | last post by:
Thanks for the replies Tristan, Eric, Steven & Kurt. They have given me some good leads. I present justification for a lot of the comments that drew (constructive) criticism below. Firstly, let...
1
by: Alex Satrapa | last post by:
Is there any (simple? har!) way to optimise a particular SQL query? At this stage, I'm more interested in making the query more readable, so I've started hunting down references about relational...
1
by: BobLaughland | last post by:
Hi All, I have a web page that loads data out of a database, and then displays it on the screen. There is a lot of logic behind the displaying, some times fields are made invisible, and...
5
by: Chris Weston | last post by:
Hi. Maybe I'm just being dim, but I'm struggling to get my head around optimising a query with regard to indexes. If I make a select query, such as a pseudo-example 'select * from bigtable...
7
by: Martin Eyles | last post by:
I have an ASP page that makes a vml/svg graph out of data. In order to extract all the necessary data I have to perform 68 SQL queries, which is unfortunatley a bit slow at the moment. For each...
4
by: PaulR | last post by:
Hi, (re: DB2 LUW v8.1 fp8 optimisation Level2) Can anyone explain why the following difference have wildly different plans - this seems very fundamental) select p.part_number,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
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: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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: 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: 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...

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.