Hi, every one
I have a big big problem in SQL Sever 2000 working with a very very big problem
This is My Story
------
I have a very big table (36,000,000 row and every day we add 400,000 new row) and i have a page ASP Classic that run Stored Procedural in Database that make ajoins betwen that table and other small tables (100,000 roww),
in this page i display some summery information that back from this Stored Procedural.
The problem is runung any query on this Stored Procedural Take from 1 hour to 3
how can i optmaize this table ? to make query run faster :eek:Hi
Please can you post the relevent DML and DDL? Please see the sticky at the top of the forum for mor info.|||Sounds like the tables aren't indexed properly...
Read the hint link at the top of the forum, or read this
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
And post what the instructions tell you to post|||sorry, it`s my first time
but what DML or DDL i have to post|||sorry, it`s my first time
but what DML or DDL i have to post
DML = Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE statements)
DDL = Data Definition Language (CREATE <object>, DROP <object>, etc)
Regards,
hmscott|||i know waht DML & DDL mean but in my case it`s not imortant|||i can`t use index because i have to insert about 6000 row every 10min
and index will slow it down|||i can`t use index because i have to insert about 6000 row every 10min
and index will slow it down WTF? So you are willing to sacrifice your application/business logic query for the sake of speedy inserts? I would put forth the thought that if your inserts are speedy as hell, it really doesn't matter if you can't access the data once it has been inserted.
In any case, this thread smells like another of our favorite types. You know, the "I know what you are telling me to do is correct, but I can't do it. Can you wave your magic wands instead?" kind of thread.
And yes, in your case the DDL and DML are certainly important.
Well, unless you are shooting for a "I know what you are telling me to do is correct, but I can't do it. Can you wave your magic wands instead?, and BTW, I am not going to tell you anything about my database, so you will need your magic x-ray glasses as well" thread.|||I know what you are telling me to do is correct, but I can't do it. Can you wave your magic wands instead?
that's called hardware. lots of it of the expensive nature. who needs efficient design and coding when you can just buy a bigger box?;)|||i can`t use index because i have to insert about 6000 row every 10min
and index will slow it down
Do tell. Have you quantified how much the slowdown is?|||that's called hardware. lots of it of the expensive nature. who needs efficient design and coding when you can just buy a bigger box?;)
We have one of those right now. The vendor wants a dedicated 8-way server running SQL 2005 to support 1,000 concurrent users and a 17 GB database.
The vendor insists that this is the only way to achieve 1,000 concurrent users and that this is "standard practice" (a dedicated database server).
:rolleyes:
Regards,
hmscott|||i can`t use index because i have to insert about 6000 row every 10min
and index will slow it down
you funny
You have an architechture problem
Do the inserts into a stage table, then have a separate process (job) that inserts the rows from stage every x minutes or so
You need an index, or you happy with mutli-hour queries?
Tell you what, I'm leaving for the weekend, and you put a big simle on my face
Thanks|||Oh, and lets see
6,000 rows every 10 minutes...which is
600 row every minute...which is
10 rows a second
And you think that's a lot?
I doubt sql server would even blink|||i can`t use index because i have to insert about 6000 row every 10min
and index will slow it downIf you don't have indexes then you have a heap. You can get better insert performance with an appropriate clustered index than a heap.
how can i optmaize this table ?
......
i know waht DML & DDL mean but in my case it`s not imortant
What you are saying is that you have a query that acts on a table and it runs like a dog. But the query and the table are irrelevent? If you go to a mechanic and tell him your car is not running properly and he asks to look under the bonnet (hood if you are American) do you think that it is irrelevant?
Another question - how many rows do you return to the ASP page?|||number of row depend on the user selection filters
i have number of dropdown list that the user will select from them,
ex: i have the date(From - to) if the user select the range 1 day it will return about 40 row, if more it more|||any idea to solve this problem|||I would suggest, as a start, that you please post the relevent DML and DDL? (Please see the sticky at the top of the forum for more info.).
Also, Sounds like the tables aren't indexed properly...
Read the hint link at the top of the forum, or read this
http://weblogs.sqlteam.com/brettk/ar...5/25/5276.aspx
And post what the instructions tell you to post.
Then we can look and take a stab at assisting you with a wave of the wand, as necessary.|||and this is my table
CREATE TABLE [dbo].[tblVoIPCDRs](
[cdrId] [decimal](19, 0) NOT NULL,
[cdrNativeID] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[cdrAccountCode] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cdrUserId] [int] NOT NULL,
[cdrUserName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cdrSrcNum] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cdrDstNum] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cdrDstField] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cdrDContext] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cdrSrcChannel] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cdrDstChannel] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cdrCallDate] [datetime] NULL,
[cdrDuration] [decimal](18, 0) NOT NULL,
[cdrBillSec] [int] NOT NULL,
[cdrDisposition] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vtrId] [int] NOT NULL,
[vrUserId] [int] NOT NULL,
[vrUserName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vdsId] [int] NOT NULL,
[vcsId] [int] NOT NULL,
[cdrCallGK] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraCallID] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraConfID] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraConnectTime] [datetime] NULL,
[meraDestinationFaststart] [smallint] NULL,
[meraDestinationTunnelling] [smallint] NULL,
[meraDialPeerName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraDisconnectCodeLocal] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraDisconnectCodeQ931] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraDisconnectTime] [datetime] NULL,
[meraDstBytesIn] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraDstBytesOut] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraDstCodec] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraDstIP] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraDstName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraDstNumberBill] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraDstNumberIn] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraDstNumberOut] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraDstRTPIP] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraDstUser] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraElapsedTime] [decimal](18, 0) NULL,
[meraHost] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraInitialIncomingLocalAddress] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraLARFaultReason] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraOutgoingLocalAddress] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraPDDReason] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraPDDTime] [decimal](18, 0) NULL,
[meraProxyMode] [int] NULL,
[meraQoS] [int] NULL,
[meraRadiusUser] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraRecordID] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraRemoteGatekeeperIP] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraRouteRetries] [smallint] NULL,
[meraSCDTime] [decimal](18, 0) NULL,
[meraSelectedIncomingLocalAddress] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraSetupTime] [datetime] NULL,
[meraSourceFaststart] [smallint] NULL,
[meraSourceTunnelling] [smallint] NULL,
[meraSrcBytesIn] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraSrcBytesOut] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraSrcCodec] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraSrcIP] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraSrcName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraSrcNumberBill] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraSrcNumberIn] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraSrcNumberOut] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraSrcRTPIP] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[meraSrcUser] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cdrActive] [smallint] NOT NULL,
[cdrInvoicedCustomer] [smallint] NOT NULL,
[cdrPaidVendor] [smallint] NOT NULL
) ON [PRIMARY]|||my stored procedral is to big i will post it into 2 parts
part 1
ALTER proc [dbo].[spVoIPCalldetailMeraPartitioned]
@.userId int=null, -- filter: user id. overrides custid when supplied
@.custId int=null, -- filter: customer id
@.vcsId int=null, -- filter: status
@.vdsId int=null, -- filter: destination id
@.beginDt datetime=null, -- filter: begin date of call
@.endDt datetime=null, -- filter: end date of call
@.srchFor nvarchar(100)=null, -- filter: searches all result fields for given text
@.sortBy varchar(200)=null, -- order by comma-seperated sort fields
@.qryMode int=1, -- 0=details 1=grandtotal 2=accountcode 3=DialCode 4:carrier 5=Country+Breakout 6=callmonth 7=calldate 8=dayofweek 9=hour 10=vendor 11=Country 12=Country & Mobile Carrier
@.detailMode int=null, -- one of above qryMode values for which detail is requested
@.detailFilter nvarchar(500)=null, -- grouping filter for detail view
@.vendor varchar(10)=null, -- grouping filter for detail view
@.vendorUserId varchar(10)=null, -- grouping filter for detail view
@.systemid varchar(3)=null -- grouping filter for detail view
as
-- set local variables
declare @.q varchar(4200) -- query
declare @.f varchar(3100) -- filter
declare @.s varchar(250) -- search string
declare @.c nvarchar(2) set @.c=char(13) -- carriage return
declare @.b nvarchar(25) -- begin date
declare @.e nvarchar(25) -- end date
declare @.dd nvarchar(100) set @.dd='Sunday Monday Tuesday Wednesday Thursday Friday Saturday'
declare @.mm nvarchar(100) set @.mm='January February March April May June July August September October November December'
declare @.t nvarchar(900) -- filter name for summary
declare @.g nvarchar(600) -- group name for summary
declare @.g1 nvarchar(15) set @.g1='''All Calls'''
declare @.g2 nvarchar(25) set @.g2='cdr.cdrAccountCode'
declare @.g3 nvarchar(200) set @.g3='isNull(cdr.vdsName + isnull('' - ''+cdr.vdsType,'''') + isnull('' - ''+cdr.vdsMobileCarrier,'''') + isnull('' - ''+cdr.vdsDescription,'''') + '' - '' + cdr.vdsDialcode, ''Unknown - System Default'')'
declare @.g4 nvarchar(55) set @.g4='isnull(cdr.vdsDescription,''Unknown'')'
declare @.g5 nvarchar(200) set @.g5=' isNull(cdr.vdsName + isnull('' - ''+cdr.vdsType,'''') + isnull('' - ''+cdr.vdsMobileCarrier,'''') + isnull('' - ''+cdr.vdsDescription,''''), ''Unknown - System Default'') '
declare @.g6 nvarchar(95) set @.g6=' mm.value + '', '' + right(year(cdr.cdrCallDate),4)'
declare @.g7 nvarchar(250) set @.g7=' ltrim(year(cdr.cdrCallDate))+right(''0''+ltrim(day (mm.idx+1)),2)+right(''0''+ltrim(day(cdr.cdrCallDa te)),2)+'' - '' + mm.value + '' '' + right(''0''+ltrim(day(cdr.cdrCallDate)),2) + '', '' + ltrim(year(cdr.cdrCallDate)) '
declare @.g8 nvarchar(85) set @.g8=' ltrim(dd.idx+1)+'' - ''+dd.value '
declare @.g9 nvarchar(250) set @.g9=' CONVERT(datetime, (CONVERT(varchar, datepart(mm,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(dd,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(yyyy,cdr.cdrCallDate)))) , datepart(hh,cdr.cdrCallDate) '
declare @.g10 nvarchar(350) set @.g10=' ISNULL(gateways.[description], ISNULL(cdr.vrUserName, ''Unknown - System Default'' )) '
declare @.g11 nvarchar(200) set @.g11='isNull(cdr.vdsName + isnull('' - ''+cdr.vdsType,''''), ''Unknown - System Default'') '
declare @.g12 nvarchar(200) set @.g12='isNull(cdr.vdsName + isnull('' - ''+cdr.vdsType,'''') + isnull('' - ''+cdr.vdsMobileCarrier,''''), ''Unknown - System Default'') '
print @.systemid
print @.sortBy
-- fix qryMode & detailMode
if @.qryMode<0 or @.qryMode>12 set @.qryMode=1
if @.detailMode<0 or @.detailMode>12 set @.detailMode=null
-- fix requested IDs
if @.userId>0 set @.custId=null if @.custId>0 set @.userId=null
-- fix requested dates
if len(@.beginDt)>0 or len(@.endDt)>0
begin
set @.beginDt=isnull(@.beginDt,'jan 01, 2000') set @.endDt=isnull(@.endDt,getdate()+1)
if @.beginDt>@.endDt set @.endDt=@.beginDt
set @.b=cast(month(@.beginDt) as varchar)+'/'+cast(day(@.beginDt) as varchar)+'/'+cast(year(@.beginDt) as varchar)+' '+cast(datepart(hh,@.beginDt) as varchar)+':'+cast(datepart(n,@.beginDt) as varchar)+':00.01'
set @.e=cast(month(@.endDt) as varchar)+'/'+cast(day(@.endDt) as varchar)+'/'+cast(year(@.endDt) as varchar)+' '+cast(datepart(hh,@.endDt) as varchar)+':'+cast(datepart(n,@.endDt) as varchar)+':59.59'
-- construct date filter
set @.f='where cdr.cdrCallDate between '''+@.b+''' and '''+@.e+''' '+@.c
end
-- fix requested search strings
set @.s=replace(replace(replace(ltrim(rtrim(@.srchFor)), '''','''),'_','[_]'),'%','[%]')
if len(@.detailFilter)>0 set @.detailFilter=replace(replace(replace(ltrim(rtrim( @.detailFilter)),'''','''),'_','_'),'%','[%]')
-- assign data grouping based on request mode
if @.qryMode=0
begin
if @.detailMode=1 begin set @.t=@.g1 end
else if @.detailMode=2 begin set @.t=@.g2 end
else if @.detailMode=3 begin set @.t=@.g3 end
else if @.detailMode=4 begin set @.t=@.g4 end
else if @.detailMode=5 begin set @.t=@.g5 end
else if @.detailMode=6 begin set @.t=@.g6 end
else if @.detailMode=7 begin set @.t=@.g7 end
else if @.detailMode=8 begin set @.t=@.g8 end
else if @.detailMode=9 begin set @.t= ' CONVERT(datetime, (CONVERT(varchar, datepart(mm,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(dd,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(yyyy,cdr.cdrCallDate))) + '' '' + convert(varchar,datepart(hh,cdr.cdrCallDate)) + '':00'') >= convert(datetime, '''+@.detailFilter+''' + '':00'') AND CONVERT(datetime, (CONVERT(varchar, datepart(mm,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(dd,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(yyyy,cdr.cdrCallDate))) + '' '' + convert(varchar,datepart(hh,cdr.cdrCallDate)) + '':00'') <= convert(datetime, '''+@.detailFilter+''' + '':59:59'') ' end
else if @.detailMode=10 begin set @.t=@.g10 end
else if @.detailMode=11 begin set @.t=@.g11 end
else if @.detailMode=12 begin set @.t=@.g12 end
end
else if @.qryMode=1 begin set @.g=@.g1 end
else if @.qryMode=2 begin set @.g=@.g2 end
else if @.qryMode=3 begin set @.g=@.g3 end
else if @.qryMode=4 begin set @.g=@.g4 end
else if @.qryMode=5 begin set @.g=@.g5 end
else if @.qryMode=6 begin set @.g=@.g6 end
else if @.qryMode=7 begin set @.g=@.g7 end
else if @.qryMode=8 begin set @.g=@.g8 end
else if @.qryMode=9 begin set @.g=@.g9 end
else if @.qryMode=10 begin set @.g=@.g10 end
else if @.qryMode=11 begin set @.g=@.g11 end
else if @.qryMode=12 begin set @.g=@.g12 end
-- construct search filter
if len(@.s)>0
begin
if len(@.f)>0 set @.f=@.f +' and ' else set @.f='where '
set @.f=@.f +' (cdr.userFN like ''%'+@.s+'%'' or cdr.userLN like ''%'+@.s+'%'' or cdr.userUnixName like ''%'+@.s+'%'' '+@.c
set @.f=@.f +' or cdr.cdrSrcNum like ''%'+@.s+'%'' or cdr.cdrSrcChannel like ''%'+@.s+'%'' or cdr.cdrDstNum like ''%'+@.s+'%'' '+@.c
set @.f=@.f +' or ''1007'' like ''%'+@.s+'%'' or cdr.vdsName like ''%'+@.s+'%'' or cdr.vcsName like ''%'+@.s+'%'' )'+@.c
end|||this is part 2
-- construct detail filter
if @.qryMode=0 and @.detailMode>0 and @.detailMode<=12 and len(@.detailFilter)>0
begin
if len(@.f)>0 set @.f=@.f +' and ' else set @.f='where '
if @.detailMode=3 set @.f=@.f +'cdr.vdsId = '''+@.detailFilter+''' ' +@.c
else set @.f=@.f +@.t +' = '''+@.detailFilter+''' ' +@.c
end
else if @.qryMode=0 and @.detailMode=9 and len(@.detailFilter)>0
begin
if len(@.f)>0 set @.f=@.f +' and ' else set @.f='where '
if @.detailMode=3 set @.f=@.f +'cdr.vdsId = '''+@.detailFilter+''' ' +@.c
else set @.f=@.f +@.t +@.c
end
-- get fields from calls table
set @.q='select '+@.c
if @.qryMode=0 -- detail mode
begin
set @.q=@.q +'cdr.custId, cdr.userId, cdr.userUnixName, cdr.userUnixName AS callSummaryName, cdr.userFN+'' ''+left(cdr.userLN,1)+''.'' userShortname, '+@.c
set @.q=@.q +'cdr.cdrCallDate AS vcdStartDt, cdr.cdrSrcNum, cdr.cdrSrcChannel, cdr.cdrAccountCode, cdr.cdrDstChannel, ''1007'' As vcdDstPrefix, cdr.cdrDstNum, isNull(cdr.vdsName + isnull('' - ''+cdr.vdsType,'''') + isnull('' - ''+cdr.vdsMobileCarrier,'''') + isnull('' - ''+cdr.vdsDescription,'''') , ''Unknown - System Default'') AS vdsName, cdr.cdrCallGK, cdr.vrUserName, '+@.c
set @.q=@.q + ' (CONVERT(varchar, datepart(mm,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(dd,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(yyyy,cdr.cdrCallDate))) AS CallDate, '
set @.q=@.q +'cdr.vcsName, cdr.vcsColor, '+@.c
set @.q=@.q +' cdr.dcqId, cdr.dcqDescription, cdr.dcqNote, '+@.c
set @.q=@.q +'cdr.cdrBillSec, ''0'' AS callRate, ''0'' AS callAdjRate, ''0'' AS callCost, cdr.vdsId AS vdsId, cdr.meraRouteRetries, cdr.meraPDDTime '+@.c
end -- summary modes: add specific output fields
else if @.qryMode=3 begin set @.q=@.q +' cdr.vdsId, ' end
if @.qryMode>0 -- summary modes: add counts
begin
set @.q=@.q + @.g +' callSummaryName, '+@.c
set @.q=@.q +'min(cdr.cdrCallDate) callMinDt, '+@.c
set @.q=@.q +'max(cdr.cdrCallDate) callMaxDt, '+@.c
set @.q=@.q +'count(*) callTotalCount, '+@.c
set @.q=@.q + ' min((CONVERT(varchar, datepart(mm,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(dd,cdr.cdrCallDate)) + ''/'' + CONVERT(varchar, datepart(yyyy,cdr.cdrCallDate)))) AS CallDate, '
set @.q=@.q +'isnull(sum(case cdr.vcsId when 2 then 1 else 0 end),0) callTotalAnswered, '+@.c
set @.q=@.q +'isnull(sum(case cdr.vcsId when 2 then 1 else 0 end),0)*100/(case count(*) when 0 then 1 else count(*) end) callTotalASR, '+@.c
set @.q=@.q +'isnull(sum(cdr.cdrBillSec),0)/(case isnull(sum(case cdr.vcsId when 2 then 1 else 0 end),0) when 0 then 1 else isnull(sum(case cdr.vcsId when 2 then 1 else 0 end),0) end) callTotalACD, '+@.c
set @.q=@.q +'isnull(sum(cdrBillSec),0) callTotalSec, '+@.c
set @.q=@.q +' AVG(CAST(IsNull(cdr1.NumberOfRetries, 0) AS Numeric)) averageRouteRetries, '+@.c
set @.q=@.q +' AVG(IsNull(cdr.meraPDDTime, 0)) averagePDD, '+@.c
set @.q=@.q + ' isnull(sum(CASE WHEN (cdr.cdrBillSec = 0) THEN (0) WHEN isnull(cdr.cdrBillSec,0) <= (6) THEN (6) ELSE ((( CASE WHEN (isnull(cdr.cdrBillSec,0)%(6))=0 then (isnull(cdr.cdrBillSec,0)%(6)) Else 6-(isnull(cdr.cdrBillSec,0)%(6)) End))+(isnull(cdr.cdrBillSec, 0)))END),0) callTotalFinal, ' + @.c
set @.q=@.q + ' isnull(sum(CASE WHEN (cdr.cdrBillSec = 0) THEN (0) WHEN isnull(cdr.cdrBillSec,0) <= (30) THEN (30) ELSE ((( CASE WHEN (isnull(cdr.cdrBillSec,0)%(6))=0 then (isnull(cdr.cdrBillSec,0)%(6)) Else 6-(isnull(cdr.cdrBillSec,0)%(6)) End ))+(isnull(cdr.cdrBillSec,0)))END),0) callTotalFinal2,' + @.c
set @.q=@.q + ' isnull(sum(CASE WHEN (cdr.cdrBillSec = 0) THEN (0) WHEN isnull(cdr.cdrBillSec,0) <= (30) THEN (30) ELSE isnull(cdr.cdrBillSec,0) END),0) callTotalFinal3, ' + @.c
set @.q=@.q + ' isnull(sum(CASE WHEN (cdr.cdrBillSec = 0) THEN (0) WHEN isnull(cdr.cdrBillSec,0) <= (60) THEN (60) ELSE ((( CASE WHEN (isnull(cdr.cdrBillSec,0)%(60))=0 then (isnull(cdr.cdrBillSec,0)%(60)) Else 60-(isnull(cdr.cdrBillSec,0)%(60)) End))+(isnull(cdr.cdrBillSec, 0)))END),0) callTotalFinal4, ' + @.c
set @.q=@.q +' 0 AS callTotalCost , MAX(cdr.vdsId) AS vdsId, '+@.c
set @.q=@.q + ' SUM(case meraDisconnectCodeQ931 when 16 then
1 else 0 end) AS NCC,
SUM(case meraDisconnectCodeQ931 when 1 then
1
when 28 then
1 else 0 end) AS WN,
SUM(case meraDisconnectCodeQ931
when 34 then
1
when 17 then
1 else 0 end) AS Busy,
SUM(case
when (meraDisconnectCodeQ931<>1) AND (meraDisconnectCodeQ931<>28) AND (meraDisconnectCodeQ931<>34) AND (meraDisconnectCodeQ931<>17) AND (meraDisconnectCodeQ931<>16) then
1 else 0 end) AS Other '
end
-- get cdr table
If @.qryMode=0
begin
set @.q=@.q +' from tblVoIPCDRTemp cdr WITH (NOLOCK) left join tblUser uu on (cdr.vrUserName=uu.userUnixName) left JOIN tblCustomer cc on (uu.CustId = cc.CustId) ' + @.c
end
Else
begin
set @.q=@.q +' from (Select MAX(cddr.cdrNativeId) AS cdrNativeId, count(*) AS NumberOfRetries from tblVoIPCDRs cddr where cddr.cdrCallDate between '''+@.b+''' and '''+@.e+''' group by cddr.meraRecordId) AS cdr1 join tblVoIPCDRTemp AS cdr WITH (NOLOCK) on (cdr1.cdrNativeId = cdr.cdrNativeId) left join tblUser uu on (cdr.vrUserName=uu.userUnixName) left JOIN tblCustomer cc on (uu.CustId = cc.CustId) ' + @.c
end
if @.qryMode=10 OR @.detailMode=10
begin
set @.q=@.q +' left outer JOIN (SELECT * FROM openquery(routing, ''SELECT * FROM gateways'') ) gateways ON gateways.[Name] = (case when patindex(''sip/%'',lower(cdr.cdrDstChannel)) > 0 then (substring(cdr.cdrDstChannel, 5, (len(cdr.cdrDstChannel)-4) - patindex(''%-%'',reverse(cdr.cdrDstChannel)))) end) ' +@.c
end
--if @.qryMode=10 begin set @.q=@.q + ' join tblCustomer cust on (vv.custID = c.custId) join X1Customer x1c on (c.x1CustId = x.ID) '+@.c end
-- join user table
--set @.q=@.q +'join tblUser u on u.userId=cd.userId '
-- join status, destination tables
--set @.q=@.q +'join tblVoIPCallstatus cs on cs.vcsId=cd.vcsId '
if @.vcsId>0
begin
if len(@.f)>0 set @.f=@.f +' and ' else set @.f='where '
set @.f=@.f +' cdr.vcsId='+cast(@.vcsId as varchar)+' '+@.c
end
else
begin
set @.f=@.f +@.c
end
--if @.userId>0 set @.q=@.q +'and u.userId='+cast(@.userId as varchar)+' '
--if @.custId>0 set @.q=@.q +'and u.custId='+cast(@.custId as varchar)+' '+@.c else set @.q=@.q +@.c
if @.userId>0
begin
if len(@.f)>0 set @.f=@.f +' and ' else set @.f='where '
set @.f=@.f +' cdr.userId='+cast(@.userId as varchar)+' '+@.c
end
else
begin
set @.f=@.f +@.c
end
if @.custId>0
begin
if len(@.f)>0 set @.f=@.f +' and ' else set @.f='where '
set @.f=@.f +' cdr.custId='+cast(@.custId as varchar)+' '+@.c
end
else
begin
set @.f=@.f +@.c
end
--set @.q=@.q +' join tblVoIPRate vr on ((vr.vtrID = cd.vtrID) AND (vr.vvdId = cd.vvdId) AND (vr.vdsID = cd.vdsID)) ' -- Join voipRATE table
--set @.q=@.q + ' join tblVoIPBillingStructure vbs on (vbs.vbsID = vr.vbsID) ' -- Join voipBillingStructure table
if @.vdsId>0
begin
if len(@.f)>0 set @.f=@.f +' and ' else set @.f='where '
set @.f=@.f +' cdr.vdsId='+cast(@.vdsId as varchar)+' '+@.c
end
else
begin
set @.f=@.f +@.c
end
if len(@.f)>0 set @.f=@.f +' and ' else set @.f='where '
if @.systemid='1' set @.f=@.f +' ( '
set @.f=@.f +' uu.systemid='''+(@.systemid)+''' '+@.c
if @.systemid='1' set @.f=@.f +' OR uu.systemid IS NULL ) '+@.c
if len(@.f)>0 set @.f=@.f +' and ' else set @.f='where '
set @.f=@.f +' cdrActive=1'+@.c
if len(@.vendor)>1
begin
if len(@.f)>0 set @.f=@.f +' and ' else set @.f='where '
set @.f=@.f +' cc.x1CustId='''+(@.vendor)+''' '+@.c
end
if len(@.vendorUserId)>1
begin
if len(@.f)>0 set @.f=@.f +' and ' else set @.f='where '
set @.f=@.f +' uu.userId='''+(@.vendorUserId)+''' '+@.c
end
-- join month and day soft tables
--if @.qryMode=6 OR @.qryMode=7 OR @.qryMode=8 OR @.qryMode=9
--Begin
set @.q=@.q +'join fnSplit('''+@.mm+''','' '') mm on mm.idx=month(cdr.cdrCallDate)-1 ' +@.c
set @.q=@.q +'join fnSplit('''+@.dd+''','' '') dd on dd.idx=datepart(dw,cdr.cdrCallDate)-1 ' +@.c
--End
-- add filter
if len(@.f)>0 set @.q=@.q +@.f
-- add grouping for summary modes
if @.qryMode=1 set @.q=@.q +'' +@.c
else if @.qryMode=3 begin set @.q=@.q +'group by cdr.vdsId, '+@.g+' ' +@.c end
else if @.qryMode in (2,4,5,6,7,8,9,10,11,12) begin set @.q=@.q +'group by '+@.g+' ' +@.c end
-- add sorting to detail mode
if len(@.sortBy)>0 and @.qryMode<>1 set @.q=@.q +'order by ' +@.sortBy +@.c
-- exec
print @.q
exec(@.q)|||Ok.
A few questions:
Please could you post a typical result from the "print @.q" line. Obviously this is building up a mega-flexible query so there are a lot of very different statements that could be built up but seeing one would be helpful.
Why are you storing cdrUserId and cdrUserName as attributes in the table? Are you familiar with normalisation? http://www.tonymarston.net/php-mysql/database-design.html
Can a cdrUserName really be in excess of 4000 international characters? Same for pretty well all the text fields.
There are no indexes, foreign keys, primary keys or constraints of any kind correct?
Also - you mentioned that this query joins to a second table. Please could you post the DDL for that too.|||I know there is no normalization here but, this system is a legacy system that built from 1990 with many programmer with differenty ideas, so i have to only optimize this table without touch anything else
so, i`m stucked|||i can`t use index because i have to insert about 6000 row every 10min
and index will slow it down
sorry, but 6000 rows in 10min is cake.|||At least from my perspective, 6000 rows in ten seconds might possibly be a challenge on a really busy server, but 6000 rows in ten minutes isn't enough work to even make a noticable bump on the performance counters on any server I'd consider running in production. This is just plain a moot point in my mind until you set up a test to see what (if any) impact adding the indicies would have.
-PatP|||I will do the following :
1- create clustered index on column Date
2- try to create temp table that will hold either summerized data or the columns that i need only|||I'm not sure the clustered index on date is necessary. That was one of reasons I asked how many rows are returned. If it is typically 40 then a non clustered index might be best.
Please could you post the answers to my last post (I know you answered the denormalised bit the rest is important too).|||any help with this problem|||See post #26 (http://www.dbforums.com/showpost.php?p=6286611&postcount=26)|||any help with this problemLol. Ok that does it. You can't be bothered reading questions and tell us they are "irrelevant" if you do.
Read up on indexes and figure it out.
http://www.sql-server-performance.com/ & BoL
Read up on appropriate data types for your table.
Read up on dynamic SQL and creating all-things-to-all-men procedures.
http://www.sommarskog.se/dynamic_sql.html|||I do the following as a solution :
1- Create View with only requried columns in table "I have on the table 71 column and i only use 16 in joins and in my stored procedural" -- that for lage table with 37,000,000 row
2-Create New table that hold some joins and some aggregation function that calculate at run time
3-Remove the joins and aggregation that occur in the Stord procedural with this new table
4-Create Clustered Index for that table
5-Create new Stored Procedural that will fill the new table each time a new data enter to the oreginal table|||If you don't have indexes then you have a heap. You can get better insert performance with an appropriate clustered index than a heap.
That can not be true|||Monotonically increasing -> quicker insert performance than a heap. I can try to dig out the source if you like (read it ages ago).|||How can inserts be faster with an index..compare having to find the correct page to put the insert (or if the page is full, then a new page), as compared to just putting at the last page?
Yes please dig up that reference, because I don't belive it|||so, create or no index|||How can inserts be faster with an index..compare having to find the correct page to put the insert (or if the page is full, then a new page), as compared to just putting at the last page?
Yes please dig up that reference, because I don't belive itThis isn't the reference I had in mind but is something:
From Kimberly Tripp (soon to be Randal if I recall correctly - I hope I got the correct guru).
http://www.sqlskills.com/blogs/kimberly/CommentView.aspx?guid=ddd63dda-4855-4724-997c-d1db2ac3fa02
Inserts are faster in a clustered table (but only in the "right" clustered table) than compared to a heap. The primary problem here is that lookups in the IAM/PFS to determine the insert location in a heap are slower than in a clustered table (where insert location is known, defined by the clustered key). Inserts are faster when inserted into a table where order is defined (CL) and where that order is ever-increasing. I have some simple numbers but I'm thinking about creating a much larger/complex scenario and publishing those. Simple/quick tests on a laptop are not always as "exciting". But - this is a well documented issue (IAM/PFS lookups) and poor performance on a heap is also referenced in this KB: PRB: Poor Performance on a Heap. note: this KB is quite dated and I don't actually agree with everything in this article however, the general concern of poor performance for inserts is still true on SQL Server 2005.The KB article she refers to:
http://support.microsoft.com/kb/297861/en-us|||In SQL Server 7.0, and later, SQL Server generally optimizes inserts into a heap with the assumption that saving space is more important than performance. That is the tradeoff you choose to make when you decide to leave a table as a heap. Therefore, an insert into a heap often spends time searching for a location to insert a new row. On the other hand, an insert to a clustered table does not have to spend time searching for space. For more details about how the space is allocated, see the "Managing Space Used by Objects" topic in SQL Server Books Online.
I'm floored...how is that possible.
Let's say you have (god forbid) an IDENTITY Column. Doesn't that incur hotspots?
And let's say you have a natural key. Doesn't the database have to skip around to find the appropriate pages...and doesn't it not only need to add the data, and manage the index...
I still don't buy it|||If you have a natural key, odds are it will not be "ever increasing".
As for the Identity, I would probably rather have a hot spot at the end of the table, than use GUIDs, and have page splits all over the table.|||I'm floored...how is that possible.
Let's say you have (god forbid) an IDENTITY Column. Doesn't that incur hotspots?
And let's say you have a natural key. Doesn't the database have to skip around to find the appropriate pages...and doesn't it not only need to add the data, and manage the index...
I still don't buy itHotspots - not a problem since 7.0 (where you been grandad?) (EDIT - in fact a hotspot is now considered a good thing - less pages in memory).
Natural key (I presume you mean not monotonically increasing) - you are quite right - this would perform worse than a heap. You would also get page splits.
Interesting though huh? Easier for SS to insert into a monotinical CI and into the B-Tree than just into a heap. Who would have confuggered it.|||Only partially sniped ;)|||I just went digging into my DB2 OS/390 manuals and a table with no idex is faster
M$ comes up with all of these "features"
I wonder how it's done...I still don't believe it...But I've seen alot of thing I couldn't believe|||I know in Sybase, there is a setting (or was a setting) that affected the decision Sybase made about adding a new page, or looking through the table for a sufficiently empty page. The fact of it all is Microsoft was faced with a choice, so they made one in favor of reducing database size, as opposed to performance. For the vast majority of tables with no primary key, this performance drop is not noticeable. Especially since most applications that can stand to have no primary keys on their tables are likely using no indexes at all, or looping through tables using cursors.|||As I recall Brett you need to revisit your logging tables' schemas - I remember a SQLTeam thread where I made my first ever post that impplied that you log stuff to heaps. I couldn't really say owt then being a fresh faced n00b rather than the seasoned pro you see before you today.|||well, when I'm logging all of sproc calls, I don;t have an index because I thought (yes I know, dangerous) that it would be fatser.
So should I put a clustered index on my datetime column, or sproc name?
I am so cornfused
:S|||So should I put a clustered index on my datetime column, or sproc name?I created an identity for mine & made it clustered. It was originally on the datetime column BUT it turned out I had one bit of looping code that called a sproc - the loop was faster than once every 3.3 milliseconds which meant I could not have a unique constraint on the datetime column. Probably the ideal would be a superkey - (DateColumn, IdentityColumn) then you can do nice range scans on the datecolumn.
You don't really mean on the sproc name do ya Brett? :shocked: :)|||That can not be true
Brett asked me to comment... Kimberly's right. Depending on the insert pattern, and as long as you pick the right index key, it'll be faster than inserts into a heap because of the free space search.
Why would IBM manuals have any bearing on how SQL server works??
Now I'm going away until next week :)
Btw - http://blogs.msdn.com/sqlserverstorageengine/archive/2007/07/30/announcements.aspx|||Sure, Brett. Bring in the big guns.
And congrats, Paul.|||Brett asked me to comment... Kimberly's right. Depending on the insert pattern, and as long as you pick the right index key, it'll be faster than inserts into a heap because of the free space search.
Thanks Paul...ummmmm, now which is the right index key?
Why would IBM manuals have any bearing on how SQL server works??
They don't, it's just my frame a of reference on how things work...I've only been doing sql server for 10 years...DB2, closer to 20|||The "right" key is one that lets SQL Server find the place to insert quickly without causing too many page splits. So your datetime column sounds like the one you would want to use (provided that means the date and time the procedure was invoked). The only time you would risk page splits is at daylight saving time, but that is only one hour per year.|||Now I'm going away until next week :)
Btw - http://blogs.msdn.com/sqlserverstorageengine/archive/2007/07/30/announcements.aspxCongrats Paul. BTW - you are half cut off your piccy on my browser. Better piccy of the happy couple: http://www.sqlskills.com/blogs/kimberly/2007/07/31/OurPerfectWeekend.aspx
And Brett - I can't believe my word was not enough and you had to ask for both links and the opinon of one of MS's premier gurus :shocked: For shame.|||Poots
I still don't belive it, and I'm going to have to do some tests
And from what I've read, it seems to be very exclusive as to when it's faster
How is stuffing rows onto the last page slower the skipping around to all different pages?
I still don't get it, and I plan to prove it|||I just want to say - I agree with Bretts logic! ;)|||I just want to say - I agree with Bretts logic! ;)You probably don't realize that posting something like that is an indicator that you are likely teetering precariously on the edge between normalcy and madness.|||Yes... so what's your point? :p|||OMG, I forgot to have a point. Damn. I better hit the bar at lunch and get my life's bearings back again.|||*Lol*
I better hit the bar at lunch
Yes, yes you should :beer:|||I thought I was driving Paul to Madness...then I realized it's just a 3 foot putt|||I thought I was driving Paul to Madness...then I realized it's just a 3 foot puttHell, I've never been farther away from it than the big clown head at the end of the course!|||How is stuffing rows onto the last page slower the skipping around to all different pages?I don't believe it is.
If you are skipping around pages rather than "stuffing them onto the last page" then you must have a key that is not ever increasing. This would be slow - much slower than a heap.
The difference is between a heap and an ever increasing clustered index. For both of these you would never skip around pages - you would fill one page and then move to the next fill that and move to the next. The performance gain for the ever increasing index is that the engine knows what the next page will be before it has finished filling the current page. When inserting to a heap the engine only figures out what the next page will be once it fills a page and instead of just filling the first data page it finds it does some search to find one towards the beginning of the file. It is this search that slows things down. I guess the point is that you would only really notice on high insert tables (or tables with moderate inserts of large rows of data).
The above is just my understanding of what's going on.
