Query longer in SP: dear

Showing posts with label dear. Show all posts

Wednesday, March 28, 2012

Query Problem

Dear All,
I have a problem about creating a view to concat all the row field
together.
For example I have a table with 2 columns
ColA, ColB
1 , A
1 , B
2 , A
2 , B
2 , C
I want to make a view using group by and the excepted view should return
ColA, ColB
1 , A-B
2 , A-B-C
Thanks for your help.
^_^You have to use Aggregate Function with Group By and since no existance sql
function to do so.. Then you have to write one by yourself, or do it in your
application
for me C# code is easier than TSQL
<Windy> wrote in message news:%233DyE7vMGHA.3708@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> I have a problem about creating a view to concat all the row field
> together.
> For example I have a table with 2 columns
> ColA, ColB
> 1 , A
> 1 , B
> 2 , A
> 2 , B
> 2 , C
> I want to make a view using group by and the excepted view should return
> ColA, ColB
> 1 , A-B
> 2 , A-B-C
>
> Thanks for your help.
> ^_^
>|||On Thu, 16 Feb 2006 21:47:45 +0800, <Windy> wrote:

>Dear All,
> I have a problem about creating a view to concat all the row field
>together.
>For example I have a table with 2 columns
>ColA, ColB
>1 , A
>1 , B
>2 , A
>2 , B
>2 , C
>I want to make a view using group by and the excepted view should return
>ColA, ColB
>1 , A-B
>2 , A-B-C
Hi Windy,
See http://www.aspfaq.com/show.asp?id=2529.
Hugo Kornelis, SQL Server MVP

Wednesday, March 21, 2012

Query Performance and Index

Dear Forum,
Please help me modify this query for optimum performance and suggest indexes
to create. Thanks. James
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinitionHi James
I added parenthesis to your query:
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,
MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND
BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
(BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
GROUP BY
BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
Please let me know if this is correct.
If it is correct, then there is no need of changing the query, your query
looks perfect.
There might be Primary Keys declared on the tables that are used here.
Else create Index on the columns that are involved in the where clause.
please let me know your comments
thanks and regards
Chandra
"James Juno" wrote:
> Dear Forum,
> Please help me modify this query for optimum performance and suggest indexes
> to create. Thanks. James
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition
>|||Adding to my previous post, what i feel is, the first OR should be replace
with AND:
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode AND
BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition
"Chandra" wrote:
> Hi James
> I added parenthesis to your query:
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,
> MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
> MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND
> BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> GROUP BY
> BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> Please let me know if this is correct.
> If it is correct, then there is no need of changing the query, your query
> looks perfect.
> There might be Primary Keys declared on the tables that are used here.
> Else create Index on the columns that are involved in the where clause.
> please let me know your comments
> thanks and regards
> Chandra
> "James Juno" wrote:
> > Dear Forum,
> >
> > Please help me modify this query for optimum performance and suggest indexes
> > to create. Thanks. James
> >
> > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition,
> > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition
> >|||Chandra,
This is great. Thank you so much. I haven't tried it yet - but it looks good.
James.
"Chandra" wrote:
> Hi James
> I added parenthesis to your query:
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,
> MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
> MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND
> BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> GROUP BY
> BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> Please let me know if this is correct.
> If it is correct, then there is no need of changing the query, your query
> looks perfect.
> There might be Primary Keys declared on the tables that are used here.
> Else create Index on the columns that are involved in the where clause.
> please let me know your comments
> thanks and regards
> Chandra
> "James Juno" wrote:
> > Dear Forum,
> >
> > Please help me modify this query for optimum performance and suggest indexes
> > to create. Thanks. James
> >
> > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition,
> > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition
> >|||To avoid confusion, use ansi join instead old style. Also use alias to make
the statement more readable.
SELECT
cc.MST_COURT_NAME,
ct.CTdefinition,
rt.RTdefinition,
c.StateFips,
MIN(substring(c.JudgmentDate, 1,6)),
MAX(substring(c.JudgmentDate, 1,6))
FROM
BBNew.dbo.fffCases as c
inner join
BBNew.dbo.fffCourtCodes as cc
on c.CourtCode = cc.MST_COURT_CODE
inner join
BBNew.dbo.fffCourtTypes as ct
on c.CourtType = ct.CTcode
inner join
BBNew.dbo.fffRecordTypes as rt
on c.FilingType = rt.RTcode
WHERE
c.StateFips LIKE :Param_State
OR c.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY
c.StateFips,
cc.MST_COURT_NAME,
ct.CTdefinition,
rt.RTdefinition;
Be sure to have an index by:
- BBNew.dbo.fffCases.PostedDate <-- clustered one
- BBNew.dbo.fffCases.StateFips
- BBNew.dbo.fffCases.CourtCode
- BBNew.dbo.fffCases.CourtType
- BBNew.dbo.fffCases.FilingType
- BBNew.dbo.fffCourtCodes.MST_COURT_CODE
- BBNew.dbo.fffCourtTypes.CTcode
- BBNew.dbo.fffRecordTypes.rt.RTcode
AMB
"James Juno" wrote:
> Chandra,
> This is great. Thank you so much. I haven't tried it yet - but it looks good.
> James.
> "Chandra" wrote:
> > Hi James
> > I added parenthesis to your query:
> > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition,
> > BBNew.dbo.fffCases.StateFips,
> > MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
> > MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> >
> > FROM
> > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> > BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> >
> > WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > AND
> > BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> >
> > (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> > OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> > OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> >
> > GROUP BY
> > BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> >
> > Please let me know if this is correct.
> >
> > If it is correct, then there is no need of changing the query, your query
> > looks perfect.
> > There might be Primary Keys declared on the tables that are used here.
> >
> > Else create Index on the columns that are involved in the where clause.
> >
> > please let me know your comments
> >
> > thanks and regards
> > Chandra
> >
> > "James Juno" wrote:
> >
> > > Dear Forum,
> > >
> > > Please help me modify this query for optimum performance and suggest indexes
> > > to create. Thanks. James
> > >
> > > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > BBNew.dbo.fffRecordTypes.RTdefinition,
> > > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > BBNew.dbo.fffRecordTypes.RTdefinition
> > >|||Alejandro,
Great. Thank you.
James
"Alejandro Mesa" wrote:
> To avoid confusion, use ansi join instead old style. Also use alias to make
> the statement more readable.
> SELECT
> cc.MST_COURT_NAME,
> ct.CTdefinition,
> rt.RTdefinition,
> c.StateFips,
> MIN(substring(c.JudgmentDate, 1,6)),
> MAX(substring(c.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCases as c
> inner join
> BBNew.dbo.fffCourtCodes as cc
> on c.CourtCode = cc.MST_COURT_CODE
> inner join
> BBNew.dbo.fffCourtTypes as ct
> on c.CourtType = ct.CTcode
> inner join
> BBNew.dbo.fffRecordTypes as rt
> on c.FilingType = rt.RTcode
> WHERE
> c.StateFips LIKE :Param_State
> OR c.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY
> c.StateFips,
> cc.MST_COURT_NAME,
> ct.CTdefinition,
> rt.RTdefinition;
> Be sure to have an index by:
> - BBNew.dbo.fffCases.PostedDate <-- clustered one
> - BBNew.dbo.fffCases.StateFips
> - BBNew.dbo.fffCases.CourtCode
> - BBNew.dbo.fffCases.CourtType
> - BBNew.dbo.fffCases.FilingType
> - BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> - BBNew.dbo.fffCourtTypes.CTcode
> - BBNew.dbo.fffRecordTypes.rt.RTcode
>
> AMB
>
> "James Juno" wrote:
> > Chandra,
> >
> > This is great. Thank you so much. I haven't tried it yet - but it looks good.
> >
> > James.
> >
> > "Chandra" wrote:
> >
> > > Hi James
> > > I added parenthesis to your query:
> > > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > BBNew.dbo.fffRecordTypes.RTdefinition,
> > > BBNew.dbo.fffCases.StateFips,
> > > MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6)),
> > > MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> > >
> > > FROM
> > > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> > > BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> > >
> > > WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > > AND
> > > BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > >
> > > (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> > > OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> > > OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> > >
> > > GROUP BY
> > > BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> > >
> > > Please let me know if this is correct.
> > >
> > > If it is correct, then there is no need of changing the query, your query
> > > looks perfect.
> > > There might be Primary Keys declared on the tables that are used here.
> > >
> > > Else create Index on the columns that are involved in the where clause.
> > >
> > > please let me know your comments
> > >
> > > thanks and regards
> > > Chandra
> > >
> > > "James Juno" wrote:
> > >
> > > > Dear Forum,
> > > >
> > > > Please help me modify this query for optimum performance and suggest indexes
> > > > to create. Thanks. James
> > > >
> > > > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > > BBNew.dbo.fffRecordTypes.RTdefinition,
> > > > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > > > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > > > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > > > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > > > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > > > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > > > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > > > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > > > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > > > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > > > BBNew.dbo.fffCourtTypes.CTdefinition,
> > > > BBNew.dbo.fffRecordTypes.RTdefinition
> > > >|||In general, it is a good practice to create a Primary Key constraint on
all tables (which will automatically create a unique index), and to
create indexes on foreign key constraints.
You did not post any DDL, so the keys and indexes cannot be reviewed.
But the query would benefit if all join columns were indexed.
Gert-Jan
James Juno wrote:
> Dear Forum,
> Please help me modify this query for optimum performance and suggest indexes
> to create. Thanks. James
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition|||Gert-Jan,
I got the script that works. Thanks for your contribution.
James
"Gert-Jan Strik" wrote:
> In general, it is a good practice to create a Primary Key constraint on
> all tables (which will automatically create a unique index), and to
> create indexes on foreign key constraints.
> You did not post any DDL, so the keys and indexes cannot be reviewed.
> But the query would benefit if all join columns were indexed.
> Gert-Jan
>
> James Juno wrote:
> >
> > Dear Forum,
> >
> > Please help me modify this query for optimum performance and suggest indexes
> > to create. Thanks. James
> >
> > SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition,
> > BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,
> > 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> > BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> > BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode => > BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> > AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> > BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> > BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> > BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> > GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> > BBNew.dbo.fffCourtTypes.CTdefinition,
> > BBNew.dbo.fffRecordTypes.RTdefinition
>

Query Performance and Index

Friday, March 9, 2012

Query Optimisation

Dear All,
I am facing problem with this procedures,the multiselect queries are
taking lot of time ,is there any other solution apart from indexes
Regards
Eckhart
CREATE proc Rolexi36Sync
as
DECLARE @.date varchar(50),@.ydate varchar(50)
print CONVERT(char(11),(GETDATE()-1),100)
SET @.date =
substring(CONVERT(char(11),(GETDATE()),1
00),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(cha
r(11),(GETDATE()-1),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()-1),1
00),8,4)
Print @.date
Print @.ydate
insert into
biiod.dbo. data_trans_currentday_test(MobileNo,UA,M
essageID,ContentID,Descrip
tion,MusicLabel,CPID,CPName,ContentType,
Category,SubCategory,TransactionDate
,Units,Unitprice,Shortcode,Servicecode,O
peratorID,CatID,SubCatID,SpecialPack
age,Royalties,Operator,Circ
le,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(phoneseries)))
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(phoneseries)))
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(phoneseries)))
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(phoneseries)))
end) as OPGPName
from datalogs.dbo.translogs where transactiondate >= @.ydate and
transactiondate < @.date and servicename in
('AIRTELMMS_SUB','ALMYALBUM646','HINDU63
97','MTV','QATAR2900','SIFY'))Eckhart wrote:
> Dear All,
> I am facing problem with this procedures,the multiselect queries are
> taking lot of time ,is there any other solution apart from indexes
Try rewriting your query, replacing subselect to inner join (if
applicable) or left join
,(select musiclabel from dbo.cont_master where contentid
=dbo.translogs.contentid) as musiclable
select ...
,m1.MusicLabel as MusicLabel
...
from dbo.translogs l [left] join dbo.cont_master M1 on
l.contentid=m1.contentid
....
if {translog.contentid} - {int NOT null} use inner join, else - u
se
left join.|||On 28 Jul 2006 00:27:31 -0700, "Eckhart" <n.kopalley@.gmail.com> wrote:
>I am facing problem with this procedures,the multiselect queries are
>taking lot of time ,is there any other solution apart from indexes
Numbers, numbers, please!
How much data, how much time?
What do you get from "set statistics io on"? What does the plan look
like?
The only thing to watch out for is if the plan somehow decides to do
the joins for all records before selecting, so you might try selecting
out of the main translog first into a #temp, then using that as the
source for the complex select. If that's still slow, either there's
something wrong with one of your lookup tables, or, well, I don't
know?!
Josh

>Regards
>Eckhart
>CREATE proc Rolexi36Sync
>as
>DECLARE @.date varchar(50),@.ydate varchar(50)
>print CONVERT(char(11),(GETDATE()-1),100)
>SET @.date =
> substring(CONVERT(char(11),(GETDATE()),1
00),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(ch
ar(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,Mu
sicLabel,CPID,CPName,ContentType,Categor
y,SubCategory,TransactionDate,Units,Unit
pric
e,Shortcode,Servicecode,OperatorID,CatID
,SubCatID,SpecialPackage,Royalties,Opera
tor,
Cir
cle,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(phoneseries)))
>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(phoneseries)))
>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(phoneseries)))
>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(phoneseries)))
>end) as OPGPName
>from datalogs.dbo.translogs where transactiondate >= @.ydate and
>transactiondate < @.date and servicename in
> ('AIRTELMMS_SUB','ALMYALBUM646','HINDU63
97','MTV','QATAR2900','SIFY'))

Query Optimisation

Dear All,
I am facing problem with this procedures,the multiselect queries are
taking lot of time ,is there any other solution apart from indexes
Regards
Eckhart
CREATE proc Rolexi36Sync
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,MessageID,ContentID,Description,MusicLabel,CPID,CPName,ContentType,Category,SubCategory,TransactionDate,Units,Unitprice,Shortcode,Servicecode,OperatorID,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(phoneseries)))
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(phoneseries)))
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(phoneseries)))
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(phoneseries)))
end) as OPGPName
from datalogs.dbo.translogs where transactiondate >= @.ydate and
transactiondate < @.date and servicename in
('AIRTELMMS_SUB','ALMYALBUM646','HINDU6397','MTV','QATAR2900','SIFY'))Eckhart wrote:
> Dear All,
> I am facing problem with this procedures,the multiselect queries are
> taking lot of time ,is there any other solution apart from indexes
Try rewriting your query, replacing subselect to inner join (if
applicable) or left join
,(select musiclabel from dbo.cont_master where contentid
=dbo.translogs.contentid) as musiclable
--
select ...
,m1.MusicLabel as MusicLabel
...
from dbo.translogs l [left] join dbo.cont_master M1 on
l.contentid=m1.contentid
....
if {translog.contentid} - {int NOT null} use inner join, else - use
left join.|||On 28 Jul 2006 00:27:31 -0700, "Eckhart" <n.kopalley@.gmail.com> wrote:
>I am facing problem with this procedures,the multiselect queries are
>taking lot of time ,is there any other solution apart from indexes
Numbers, numbers, please!
How much data, how much time?
What do you get from "set statistics io on"? What does the plan look
like?
The only thing to watch out for is if the plan somehow decides to do
the joins for all records before selecting, so you might try selecting
out of the main translog first into a #temp, then using that as the
source for the complex select. If that's still slow, either there's
something wrong with one of your lookup tables, or, well, I don't
know?!
Josh
>Regards
>Eckhart
>CREATE proc Rolexi36Sync
>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,MessageID,ContentID,Description,MusicLabel,CPID,CPName,ContentType,Category,SubCategory,TransactionDate,Units,Unitprice,Shortcode,Servicecode,OperatorID,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(phoneseries)))
>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(phoneseries)))
>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(phoneseries)))
>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(phoneseries)))
>end) as OPGPName
>from datalogs.dbo.translogs where transactiondate >= @.ydate and
>transactiondate < @.date and servicename in
>('AIRTELMMS_SUB','ALMYALBUM646','HINDU6397','MTV','QATAR2900','SIFY'))

Wednesday, March 28, 2012

Query Problem

Wednesday, March 21, 2012

Query Performance and Index

Query Performance and Index

Query Performance and Index

Friday, March 9, 2012

Query Optimisation

Query Optimisation

Query longer in SP

Blog Archive

About Me