OpenXML Identity Insert: performance

Showing posts with label performance. Show all posts

Friday, March 30, 2012

Optimizing SQL Query performance

Hi,
I have collected SQL Trace events into a trace table by exporting the trace
into a table.
I have a table Trace1 with following columns:
-RowNumber
-ApplicationName
-DatabaseName
.
.
.
-StartTime
-EndTime
Clustered Index on RowNumber Column.
NonClustered Index on StartTime Column.
Trace1 table contains 100,000 of rows.
Now I am firing a query something like
"select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
But above query gives me timeout error for most of the cases. I have
specified timeout value as 60 seconds.
How can I solve this timeout issue?
Do I need to have some other proper indexes, if current indexes are not
proper?
Do I need to increase the timeout value? What is the optimum value of
Timeout in such scenarios? I am expecting that this table is going to grow
to contain atleast 5 crores of rows. So please suggest what strategy should
I adopt?
Thanks,
PushkarIf you have an index on StartTime, this should be the most efficient way to
retrieve the data. I would not expect 100,000 rows to take more than a
moment. Even without an index, I would expect the query to finish in
seconds.
Check your execution plan and see if it is using the index.
You could try regenerating your statistics on this table, which should get
it to use this index.
Post the full DDL of your table including the indexes themselves, just so we
are perfectly clear on what you have.
http://www.aspfaq.com/etiquette.asp?id=5006
I think the likely culprit here is the use of "Select * ". If this is a
very wide table, you may be timing out moving all of that data across the
network. Also, how many rows does your typical date range select? If you
usually return 90,000 rows, that makes a big difference. If every row
contains 1 meg of data (an extreme case, just to illustrate a point), for
example, that would be 90 gigs moving over the network, and would timeout
every time.
Also, what application are you using to run the query?
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:%23aMdJOHaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have collected SQL Trace events into a trace table by exporting the
trace
> into a table.
> I have a table Trace1 with following columns:
> -RowNumber
> -ApplicationName
> -DatabaseName
> .
> .
> .
> -StartTime
> -EndTime
> Clustered Index on RowNumber Column.
> NonClustered Index on StartTime Column.
> Trace1 table contains 100,000 of rows.
> Now I am firing a query something like
> "select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
> But above query gives me timeout error for most of the cases. I have
> specified timeout value as 60 seconds.
> How can I solve this timeout issue?
> Do I need to have some other proper indexes, if current indexes are not
> proper?
> Do I need to increase the timeout value? What is the optimum value of
> Timeout in such scenarios? I am expecting that this table is going to grow
> to contain atleast 5 crores of rows. So please suggest what strategy
should
> I adopt?
> Thanks,
> Pushkar
>
>
>|||Pushkar,
It depends on the relative amount of rows that the query returns.
I would start with adding a (nonclustered) index on StartTime. If the
query returns just a few percent of all rows and tables rows are
relatively wide as compared to the StartTime column, then this index
will probably be used.
If the query returns more than a few percent, or the rows are narrow,
then a nonclustered index on StartTime might be ignored. If this query
is one of the most important queries in your system, then you could
create a clustered index on StartTime (you will need to change the
current clustered index to nonclustered).
HTH,
Gert-Jan
Pushkar wrote:
> Hi,
> I have collected SQL Trace events into a trace table by exporting the trac
e
> into a table.
> I have a table Trace1 with following columns:
> -RowNumber
> -ApplicationName
> -DatabaseName
> .
> .
> .
> -StartTime
> -EndTime
> Clustered Index on RowNumber Column.
> NonClustered Index on StartTime Column.
> Trace1 table contains 100,000 of rows.
> Now I am firing a query something like
> "select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
> But above query gives me timeout error for most of the cases. I have
> specified timeout value as 60 seconds.
> How can I solve this timeout issue?
> Do I need to have some other proper indexes, if current indexes are not
> proper?
> Do I need to increase the timeout value? What is the optimum value of
> Timeout in such scenarios? I am expecting that this table is going to grow
> to contain atleast 5 crores of rows. So please suggest what strategy shoul
d
> I adopt?
> Thanks,
> Pushkar

Optimizing SQL 2000 SP4 for large amount of memory

Hi
We will within the next week move our SQL 2000 server SP4 to a new high
performance server (2 x ghz XEON, 4 GB RAM)
The database which is to be running on the server has a physical size of 50
mb on disk.
Is there a way to optimize the SQL 2000 to keep as much as possible
in-memory so searching will as quick as possible? The read / write rate is
approx 1000/1 (we read 1000 x as often as writing)
Is the some memory settings we can tweak or are we better of letting sql
server 2000 handle it?
Thanks in regards
Anders JacobsenJust checking 50Mb not 50 Gb. If so then there's not much you can do, with
4Gb of RAM it will all reside in memory.
--
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
> 50 mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
> Thanks in regards
> Anders Jacobsen
>|||"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
50
> mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
Generally you're better off letting SQL Server handle it. It will grab as
much RAM as it can (2GB with Standard, more with Enterprise with the proper
switches) and use it to cache.
Check perfmon and look at the cache hit ratio for one thing to see how
you're doing.
If it's truly 50MB, that'll fit into RAM absolutely w/o problems.
> Thanks in regards
> Anders Jacobsen
>|||> Generally you're better off letting SQL Server handle it. It will grab as
> much RAM as it can (2GB with Standard, more with Enterprise with the
> proper
> switches) and use it to cache.
> Check perfmon and look at the cache hit ratio for one thing to see how
> you're doing.
> If it's truly 50MB, that'll fit into RAM absolutely w/o problems.
Sounds great.
Thanks

optimizing query performance

Hi,
can I have your ideas about which database structural and physical elements
I would review in order to optimize some query performance?
Thank you,
ManuTop wrote:
> Hi,
> can I have your ideas about which database structural and physical
> elements I would review in order to optimize some query performance?
> Thank you,
Start by looking at the SQL that's running on the server. You can do
this from Profiler using the SQL:BatchCompleted and RPC:Completed
events. Have a look at the CPU, Duration, and Reads columns. Once you
have a good idea what's not running efficiently, you'll know what
objects to look at for tuning.
Imceda offers Coefficient, a SQL performance analysis tool. You can
download a trial from our web site.
David Gugick
Imceda Software
www.imceda.com
sql

optimizing load performance using partitioned tables in 2005

Hi,
Iam curious to know if i can increase my load performance using
partitioned tables. If i create a 4-way partitioned table, can i load
directly into a specific partition, so effectively having 4 parallel loads
into the 4 partitions. (Something i can do in Sybase).
If not what can i do to maximize my load on a partitioned table?
Vivek
This is one option, but does your data align itself with these partitions?
Supposed you partition on last name, are your last names going to be evenly
distributed? Last names are a pretty good choice as the distribution is
somewhat even, however a choice like date is probably not so good if your
data is ordered already.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vivek" <Vivek@.discussions.microsoft.com> wrote in message
news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
> Hi,
> Iam curious to know if i can increase my load performance using
> partitioned tables. If i create a 4-way partitioned table, can i load
> directly into a specific partition, so effectively having 4 parallel loads
> into the 4 partitions. (Something i can do in Sybase).
> If not what can i do to maximize my load on a partitioned table?
> Vivek
|||Yes my data does align itself with these partitions. So what i want to know
is how do I load data into a specific partition? (using say BCP or Bulk
Insert) What is the syntax?
"Hilary Cotter" wrote:

> This is one option, but does your data align itself with these partitions?
> Supposed you partition on last name, are your last names going to be evenly
> distributed? Last names are a pretty good choice as the distribution is
> somewhat even, however a choice like date is probably not so good if your
> data is ordered already.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Vivek" <Vivek@.discussions.microsoft.com> wrote in message
> news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
>
>

optimizing load performance using partitioned tables in 2005

Hi,
Iam curious to know if i can increase my load performance using
partitioned tables. If i create a 4-way partitioned table, can i load
directly into a specific partition, so effectively having 4 parallel loads
into the 4 partitions. (Something i can do in Sybase).
If not what can i do to maximize my load on a partitioned table?
VivekThis is one option, but does your data align itself with these partitions?
Supposed you partition on last name, are your last names going to be evenly
distributed? Last names are a pretty good choice as the distribution is
somewhat even, however a choice like date is probably not so good if your
data is ordered already.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vivek" <Vivek@.discussions.microsoft.com> wrote in message
news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
> Hi,
> Iam curious to know if i can increase my load performance using
> partitioned tables. If i create a 4-way partitioned table, can i load
> directly into a specific partition, so effectively having 4 parallel loads
> into the 4 partitions. (Something i can do in Sybase).
> If not what can i do to maximize my load on a partitioned table?
> Vivek|||Yes my data does align itself with these partitions. So what i want to know
is how do I load data into a specific partition? (using say BCP or Bulk
Insert) What is the syntax?
"Hilary Cotter" wrote:

> This is one option, but does your data align itself with these partitions?
> Supposed you partition on last name, are your last names going to be evenl
y
> distributed? Last names are a pretty good choice as the distribution is
> somewhat even, however a choice like date is probably not so good if your
> data is ordered already.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Vivek" <Vivek@.discussions.microsoft.com> wrote in message
> news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
>
>

optimizing load performance using partitioned tables in 2005

Hi,
Iam curious to know if i can increase my load performance using
partitioned tables. If i create a 4-way partitioned table, can i load
directly into a specific partition, so effectively having 4 parallel loads
into the 4 partitions. (Something i can do in Sybase).
If not what can i do to maximize my load on a partitioned table?
VivekThis is one option, but does your data align itself with these partitions?
Supposed you partition on last name, are your last names going to be evenly
distributed? Last names are a pretty good choice as the distribution is
somewhat even, however a choice like date is probably not so good if your
data is ordered already.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vivek" <Vivek@.discussions.microsoft.com> wrote in message
news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
> Hi,
> Iam curious to know if i can increase my load performance using
> partitioned tables. If i create a 4-way partitioned table, can i load
> directly into a specific partition, so effectively having 4 parallel loads
> into the 4 partitions. (Something i can do in Sybase).
> If not what can i do to maximize my load on a partitioned table?
> Vivek|||Yes my data does align itself with these partitions. So what i want to know
is how do I load data into a specific partition? (using say BCP or Bulk
Insert) What is the syntax?
"Hilary Cotter" wrote:
> This is one option, but does your data align itself with these partitions?
> Supposed you partition on last name, are your last names going to be evenly
> distributed? Last names are a pretty good choice as the distribution is
> somewhat even, however a choice like date is probably not so good if your
> data is ordered already.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Vivek" <Vivek@.discussions.microsoft.com> wrote in message
> news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
> >
> > Hi,
> >
> > Iam curious to know if i can increase my load performance using
> > partitioned tables. If i create a 4-way partitioned table, can i load
> > directly into a specific partition, so effectively having 4 parallel loads
> > into the 4 partitions. (Something i can do in Sybase).
> > If not what can i do to maximize my load on a partitioned table?
> >
> > Vivek
>
>

Wednesday, March 28, 2012

Optimizing insert performance

Hi there.
We're using SQL Server 2000 on a Windows 2000 box with dual
hyperthreaded Intel CPUs, raided hard disks and 2GB RAM. It should
certainly be able to handle our currently fairly modest demands. We
have all the latest security patches. Parallelism is disabled.
We're currently experiencing unacceptable slowness for inserts to a
particular table, Account_Transaction (see the DDL at the end of this
message). As you can see, the main features are a BIGINT auto-increment
primary key, and three foreign keys. This table currently has 7 million
rows and another million are inserted each month. We are currently
unhappy with the length of time it takes to insert into this table as
this operation is at the heart of our application.
Here is an example insert, called from within a stored procedure:
INSERT INTO dbo.Account_Transaction(
invoice_id, transaction_type_id, qty, amount, commission,
tax_percentage, currency_code, points, line_id, comment,
balance_before, external_id)
VALUES
(
@.invoice_id,
@.transaction_type_id,
@.qty,
@.amount,
@.amount_commission,
@.tax_percentage,
@.currency_code_home,
@.points,
@.line_id,
@.comment,
@.balance,
@.external_id
)
Looking at this query's SP:StmtCompleted event from the profiler we can
see the query took 375ms to complete and performed 76 reads (and
apparently zero writes, although the data was definitely inserted). We
think this is far too long.
What is strange though is that if we run the exact same statement
OUTSIDE of the calling procedure in query analyzer, substituting
literals for the variables, then it apparently completes in just 63ms,
although it performs the same number of writes:
INSERT INTO dbo.Account_Transaction(
invoice_id, transaction_type_id, qty, amount, commission,
tax_percentage, currency_code, points, line_id, comment,
balance_before, external_id)
VALUES
(6842289, 12, 1, 1, 0, 10, 'AUD', 0, 4, 'test', 0, 0)
If we now try the same two things on our test database, running on a a
bog-standard desktop machine, with only 300000 rows in the
Account_Transaction table, then both queries execute in similar times of
around 70ms.
So my question is - what is causing the slowness, and how can we speed
it up? How do we make the insert operation from within the SP take the
same amount of time as it takes when run on its own?
Any help appreciated,
Mike C
CREATE TABLE [dbo].[Account_Transaction] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[invoice_id] [bigint] NOT NULL ,
[transaction_type_id] [int] NOT NULL ,
[qty] [int] NOT NULL ,
[amount] [money] NOT NULL ,
[tax_percentage] [decimal](9, 3) NULL ,
[commission] [float] NOT NULL ,
[currency_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[line_id] [int] NOT NULL ,
[points] [int] NOT NULL ,
[comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[balance_before] [money] NOT NULL ,
[balance_after] [money] NULL ,
[external_id] [bigint] NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
CONSTRAINT [PK_Account_Transaction] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
CONSTRAINT [DF__Account_T__amoun__3118447E] DEFAULT (0) FOR [amount],
CONSTRAINT [DF__Account_T__point__320C68B7] DEFAULT (0) FOR [points],
CONSTRAINT [DF__Account_T__msrep__31783731] DEFAULT (newid()) FOR
[msrepl_tran_version]
GO
CREATE INDEX [IX_Account_Transaction] ON
[dbo].[Account_Transaction]([invoice_id]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account_Transaction] ADD
CONSTRAINT [FK_Account_Transaction_Account_Invoice]
FOREIGN KEY
(
[invoice_id]
) REFERENCES [dbo].[Account_Invoice] (
[id]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Account_Transaction_Line] FOREIGN KEY
(
[line_id]
) REFERENCES [dbo].[Line] (
[id]
),
CONSTRAINT [FK_Account_Transaction_Transaction_Type
] FOREIGN KEY
(
[transaction_type_id]
) REFERENCES [dbo].[Transaction_Type] (
[id]
)
GOMike
1) Make sure that your table has no triggers
2) Try drop the indexes just prior to INSERTING and re-create them after
Another thing might help that if you do it by using SP which accepted
parameters try to re-assign these parameters to local variable
CREATE PROC myProc
@.par INT
AS
DECLARE @.lpar INT
SET @.lpar =@.par
INSERT INTO Table VALUES (@.lpar )
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:eGXwhg%23HGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi there.
> We're using SQL Server 2000 on a Windows 2000 box with dual hyperthreaded
> Intel CPUs, raided hard disks and 2GB RAM. It should certainly be able to
> handle our currently fairly modest demands. We have all the latest
> security patches. Parallelism is disabled.
> We're currently experiencing unacceptable slowness for inserts to a
> particular table, Account_Transaction (see the DDL at the end of this
> message). As you can see, the main features are a BIGINT auto-increment
> primary key, and three foreign keys. This table currently has 7 million
> rows and another million are inserted each month. We are currently
> unhappy with the length of time it takes to insert into this table as this
> operation is at the heart of our application.
> Here is an example insert, called from within a stored procedure:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (
> @.invoice_id,
> @.transaction_type_id,
> @.qty,
> @.amount,
> @.amount_commission,
> @.tax_percentage,
> @.currency_code_home,
> @.points,
> @.line_id,
> @.comment,
> @.balance,
> @.external_id
> )
> Looking at this query's SP:StmtCompleted event from the profiler we can
> see the query took 375ms to complete and performed 76 reads (and
> apparently zero writes, although the data was definitely inserted). We
> think this is far too long.
> What is strange though is that if we run the exact same statement OUTSIDE
> of the calling procedure in query analyzer, substituting literals for the
> variables, then it apparently completes in just 63ms, although it performs
> the same number of writes:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (6842289, 12, 1, 1, 0, 10, 'AUD', 0, 4, 'test', 0, 0)
> If we now try the same two things on our test database, running on a a
> bog-standard desktop machine, with only 300000 rows in the
> Account_Transaction table, then both queries execute in similar times of
> around 70ms.
> So my question is - what is causing the slowness, and how can we speed it
> up? How do we make the insert operation from within the SP take the same
> amount of time as it takes when run on its own?
> Any help appreciated,
> --
> Mike C
>
>
> CREATE TABLE [dbo].[Account_Transaction] (
> [id] [bigint] IDENTITY (1, 1) NOT NULL ,
> [invoice_id] [bigint] NOT NULL ,
> [transaction_type_id] [int] NOT NULL ,
> [qty] [int] NOT NULL ,
> [amount] [money] NOT NULL ,
> [tax_percentage] [decimal](9, 3) NULL ,
> [commission] [float] NOT NULL ,
> [currency_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [line_id] [int] NOT NULL ,
> [points] [int] NOT NULL ,
> [comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [balance_before] [money] NOT NULL ,
> [balance_after] [money] NULL ,
> [external_id] [bigint] NULL ,
> [msrepl_tran_version] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [PK_Account_Transaction] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [DF__Account_T__amoun__3118447E] DEFAULT (0) FOR [amount],
> CONSTRAINT [DF__Account_T__point__320C68B7] DEFAULT (0) FOR [points],
> CONSTRAINT [DF__Account_T__msrep__31783731] DEFAULT (newid()) FOR
> [msrepl_tran_version]
> GO
> CREATE INDEX [IX_Account_Transaction] ON
> [dbo].[Account_Transaction]([invoice_id]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] ADD
> CONSTRAINT [FK_Account_Transaction_Account_Invoice]
FOREIGN KEY
> (
> [invoice_id]
> ) REFERENCES [dbo].[Account_Invoice] (
> [id]
> ) ON DELETE CASCADE ON UPDATE CASCADE ,
> CONSTRAINT [FK_Account_Transaction_Line] FOREIGN KEY
> (
> [line_id]
> ) REFERENCES [dbo].[Line] (
> [id]
> ),
> CONSTRAINT [FK_Account_Transaction_Transaction_Type
] FOREIGN KEY
> (
> [transaction_type_id]
> ) REFERENCES [dbo].[Transaction_Type] (
> [id]
> )
> GO|||It sounds like Mike is speaking of single transactional inserts and not bulk
inserts, so dropping / recreating indexes on the table would not be
practical.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23Unqxm%23HGHA.528@.TK2MSFTNGP12.phx.gbl...
> Mike
> 1) Make sure that your table has no triggers
> 2) Try drop the indexes just prior to INSERTING and re-create them after
>
> Another thing might help that if you do it by using SP which accepted
> parameters try to re-assign these parameters to local variable
>
> CREATE PROC myProc
> @.par INT
> AS
> DECLARE @.lpar INT
>
> SET @.lpar =@.par
> INSERT INTO Table VALUES (@.lpar )
>
>
> "Mike Chamberlain" <none@.hotmail.com> wrote in message
> news:eGXwhg%23HGHA.1180@.TK2MSFTNGP09.phx.gbl...
>|||Consider if the stored procedure is re-compiling each time it is executed.
This could explain the overhead when executing the insert from the procedure
while it still performs the same number of writes as the test insert from
Query Analyzer.
INF: Conditions for Stored Procedure Recompilation
http://support.microsoft.com/defaul...kb;en-us;104445
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/defaul...kb;en-us;308737
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:eGXwhg%23HGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi there.
> We're using SQL Server 2000 on a Windows 2000 box with dual hyperthreaded
> Intel CPUs, raided hard disks and 2GB RAM. It should certainly be able to
> handle our currently fairly modest demands. We have all the latest
> security patches. Parallelism is disabled.
> We're currently experiencing unacceptable slowness for inserts to a
> particular table, Account_Transaction (see the DDL at the end of this
> message). As you can see, the main features are a BIGINT auto-increment
> primary key, and three foreign keys. This table currently has 7 million
> rows and another million are inserted each month. We are currently
> unhappy with the length of time it takes to insert into this table as this
> operation is at the heart of our application.
> Here is an example insert, called from within a stored procedure:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (
> @.invoice_id,
> @.transaction_type_id,
> @.qty,
> @.amount,
> @.amount_commission,
> @.tax_percentage,
> @.currency_code_home,
> @.points,
> @.line_id,
> @.comment,
> @.balance,
> @.external_id
> )
> Looking at this query's SP:StmtCompleted event from the profiler we can
> see the query took 375ms to complete and performed 76 reads (and
> apparently zero writes, although the data was definitely inserted). We
> think this is far too long.
> What is strange though is that if we run the exact same statement OUTSIDE
> of the calling procedure in query analyzer, substituting literals for the
> variables, then it apparently completes in just 63ms, although it performs
> the same number of writes:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (6842289, 12, 1, 1, 0, 10, 'AUD', 0, 4, 'test', 0, 0)
> If we now try the same two things on our test database, running on a a
> bog-standard desktop machine, with only 300000 rows in the
> Account_Transaction table, then both queries execute in similar times of
> around 70ms.
> So my question is - what is causing the slowness, and how can we speed it
> up? How do we make the insert operation from within the SP take the same
> amount of time as it takes when run on its own?
> Any help appreciated,
> --
> Mike C
>
>
> CREATE TABLE [dbo].[Account_Transaction] (
> [id] [bigint] IDENTITY (1, 1) NOT NULL ,
> [invoice_id] [bigint] NOT NULL ,
> [transaction_type_id] [int] NOT NULL ,
> [qty] [int] NOT NULL ,
> [amount] [money] NOT NULL ,
> [tax_percentage] [decimal](9, 3) NULL ,
> [commission] [float] NOT NULL ,
> [currency_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [line_id] [int] NOT NULL ,
> [points] [int] NOT NULL ,
> [comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [balance_before] [money] NOT NULL ,
> [balance_after] [money] NULL ,
> [external_id] [bigint] NULL ,
> [msrepl_tran_version] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [PK_Account_Transaction] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [DF__Account_T__amoun__3118447E] DEFAULT (0) FOR [amount],
> CONSTRAINT [DF__Account_T__point__320C68B7] DEFAULT (0) FOR [points],
> CONSTRAINT [DF__Account_T__msrep__31783731] DEFAULT (newid()) FOR
> [msrepl_tran_version]
> GO
> CREATE INDEX [IX_Account_Transaction] ON
> [dbo].[Account_Transaction]([invoice_id]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] ADD
> CONSTRAINT [FK_Account_Transaction_Account_Invoice]
FOREIGN KEY
> (
> [invoice_id]
> ) REFERENCES [dbo].[Account_Invoice] (
> [id]
> ) ON DELETE CASCADE ON UPDATE CASCADE ,
> CONSTRAINT [FK_Account_Transaction_Line] FOREIGN KEY
> (
> [line_id]
> ) REFERENCES [dbo].[Line] (
> [id]
> ),
> CONSTRAINT [FK_Account_Transaction_Transaction_Type
] FOREIGN KEY
> (
> [transaction_type_id]
> ) REFERENCES [dbo].[Transaction_Type] (
> [id]
> )
> GOsql

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Hi, as per the BOL, In the "Optimizing Data Warehouse Query Performance Through Bitmap Filtering"

the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai according to sql Server 2008.

Can you verify it please.

Hi Akash,

What statements in the topic led you to that conclusion? I wrote the topic, so I want to make sure I correct any such sentences that can be perceived incorrectly.

Thanks,

Gail

|||

Hi Akash,

technically, there is no reason for higher cost of scans. Could you please point Gail to the BOL topic so that we can make sure that it is accurate?

Thanks,

Torsten Grabs [MSFT]

Program Manager

SQL Server Query Processor

|||

Hi Gail,

As i've mentioned that in the BOL when you type "Bitmap Index", you let the user understand how optimized bitmap filtering is implemented. You also demonstrate an example for it comparing both the SQL Server 2005 along with Katmai and in that very example you did mention that the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai when compared to sql Server 2005. Just have a look at the figures.

Clustered index scan costs 3% for DimProduct & 12% for DimCustomer in case of SQL Server 2008 where as the same clustering index scan costs 2% and 11% respectively in SQL Server 2005.

Similarly Table Scan costs 14% in SQL Server 2008 and 13% in case of SQL Server 2005.

Should not there be a decrese in scanning cost in Katmai when compared to SQL Server 2005 ?

|||

Hi Akash,

Thanks very much for the pointer to the BOL entry you were referring to. There are two considerations two keep in mind when comparing the table scan cost information as you do:

- Costing functions may change between releases. Hence, when you compare query plan costs and operator costs across releases, you are on unstable ground as you make the assumption that the costing function has not been changed.

- The percentages are relative costs. The less expensive the remaining query plan, i.e., the plan on top of the table scan, gets the higher this percentage value will be - assuming that the cost of the scan is the same. If you look into the subtree cost information of the scan, you will see that the cost is the same for both plans. But, the remaining operators, in particular the hash joins on top, have become cheaper since there are fewer rows to process - which explain why you see the percentages increase.

A further note on why the absolute costs between the scan operators remain the same: even with bitmap filters, the table scan operation has to read all the rows from the disk or buffer pool initially. However, as soon as the row is read into the query processor, we can apply the bitmap filter. The main cost, however, is spent in disk or buffer pool reads - the bitmap filter processing itself is very cheap. Therefore, we do not model the bitmap filtering cost in plan generation. However, optimized bitmap filters do model the cardinality changes of the bitmap filter on top of the scan operation.

Hope this explains the showplan information in the BOL topic.

Best regards,

Torsten Grabs [MSFT]

Program Manager

Microsoft SQL Server Query Processor

|||Thanks Torsten. It was really a great help,appreciate your effort in letting me understand this

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Hi, as per the BOL, In the "Optimizing Data Warehouse Query Performance Through Bitmap Filtering"

the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai according to sql Server 2008.

Can you verify it please.

Hi Akash,

What statements in the topic led you to that conclusion? I wrote the topic, so I want to make sure I correct any such sentences that can be perceived incorrectly.

Thanks,

Gail

|||

Hi Akash,

technically, there is no reason for higher cost of scans. Could you please point Gail to the BOL topic so that we can make sure that it is accurate?

Thanks,

Torsten Grabs [MSFT]

Program Manager

SQL Server Query Processor

|||

Hi Gail,

Clustered index scan costs 3% for DimProduct & 12% for DimCustomer in case of SQL Server 2008 where as the same clustering index scan costs 2% and 11% respectively in SQL Server 2005.

Similarly Table Scan costs 14% in SQL Server 2008 and 13% in case of SQL Server 2005.

Should not there be a decrese in scanning cost in Katmai when compared to SQL Server 2005 ?

|||

Hi Akash,

Thanks very much for the pointer to the BOL entry you were referring to. There are two considerations two keep in mind when comparing the table scan cost information as you do:

Hope this explains the showplan information in the BOL topic.

Best regards,

Torsten Grabs [MSFT]

Program Manager

Microsoft SQL Server Query Processor

|||Thanks Torsten. It was really a great help,appreciate your effort in letting me understand this

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Hi, as per the BOL, In the "Optimizing Data Warehouse Query Performance Through Bitmap Filtering"

the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai according to sql Server 2008.

Can you verify it please.

Hi Akash,

What statements in the topic led you to that conclusion? I wrote the topic, so I want to make sure I correct any such sentences that can be perceived incorrectly.

Thanks,

Gail

|||

Hi Akash,

technically, there is no reason for higher cost of scans. Could you please point Gail to the BOL topic so that we can make sure that it is accurate?

Thanks,

Torsten Grabs [MSFT]

Program Manager

SQL Server Query Processor

|||

Hi Gail,

Clustered index scan costs 3% for DimProduct & 12% for DimCustomer in case of SQL Server 2008 where as the same clustering index scan costs 2% and 11% respectively in SQL Server 2005.

Similarly Table Scan costs 14% in SQL Server 2008 and 13% in case of SQL Server 2005.

Should not there be a decrese in scanning cost in Katmai when compared to SQL Server 2005 ?

|||

Hi Akash,

Thanks very much for the pointer to the BOL entry you were referring to. There are two considerations two keep in mind when comparing the table scan cost information as you do:

Hope this explains the showplan information in the BOL topic.

Best regards,

Torsten Grabs [MSFT]

Program Manager

Microsoft SQL Server Query Processor

|||

Thanks Torsten. It was really a great help,appreciate your effort in letting me understand this

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Hi, as per the BOL, In the "Optimizing Data Warehouse Query Performance Through Bitmap Filtering"

the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai according to sql Server 2008.

Can you verify it please.

Hi Akash,

What statements in the topic led you to that conclusion? I wrote the topic, so I want to make sure I correct any such sentences that can be perceived incorrectly.

Thanks,

Gail

|||

Hi Akash,

technically, there is no reason for higher cost of scans. Could you please point Gail to the BOL topic so that we can make sure that it is accurate?

Thanks,

Torsten Grabs [MSFT]

Program Manager

SQL Server Query Processor

|||

Hi Gail,

Clustered index scan costs 3% for DimProduct & 12% for DimCustomer in case of SQL Server 2008 where as the same clustering index scan costs 2% and 11% respectively in SQL Server 2005.

Similarly Table Scan costs 14% in SQL Server 2008 and 13% in case of SQL Server 2005.

Should not there be a decrese in scanning cost in Katmai when compared to SQL Server 2005 ?

|||

Hi Akash,

Thanks very much for the pointer to the BOL entry you were referring to. There are two considerations two keep in mind when comparing the table scan cost information as you do:

Hope this explains the showplan information in the BOL topic.

Best regards,

Torsten Grabs [MSFT]

Program Manager

Microsoft SQL Server Query Processor

|||Thanks Torsten. It was really a great help,appreciate your effort in letting me understand this

sql

Optimizing cursor performance?

Hi there!
I have an application that uses cursors en mass in SQL. Actually, it does not
speak one word relational SQL with MSSQL, only cursors. I think it has a fair
bet in becoming World Champion in cursor abuse.
Anyway, the application is almost pure sequential/flat file based, and thus I
wondered, is there anything I can do to MSSQL that pulls out every bit of cursor
performance available, as it is very much needed?
Microsoft knows the problems, as they themself bought this very application..
I doubt, therefore I might be.
Kim
Cursors are never (mostly) good choice. Without seeing your data ,it's hard
to suggest something.
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>
|||I have found in many cases that declaring the cursor as Static can often
lead to increased performance but as always it depends<g>.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ov$xEZNvFHA.3864@.TK2MSFTNGP12.phx.gbl

> Cursors are never (mostly) good choice. Without seeing your data
> ,it's hard to suggest something.
I know, that's why I'm trying to be slightly sarcastic in my message

.
What do you need of information? Unfortunately I can only pull out limited
information on this, as the application is closed source.
I doubt, therefore I might be.
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> I have found in many cases that declaring the cursor as Static can
> often lead to increased performance but as always it depends<g>.
But is there anything more 'high level' I can do? What does cursors appreciate
of hardware; same as with relational SQL?
The fun thing is that the application doesn't seem to care much about extra RAM.
Even when the SQL server sucks up 1.5GB versus 500MB, I still don't see any
performance improvements noteworthy.
Almost seems like there's some hardwired cap on max cursor performance

.
I doubt, therefore I might be.
|||Depending on how they are configured and such they may use a lot of Tempdb.
So making sure tempdb is on a fast drive array might help. They tend to use
a fair amount of cpu as well. But there is no magic switch to make cursors
go faster. The real answer is sadly to rewrite them to not use cursors.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:udfAjDSvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> But is there anything more 'high level' I can do? What does cursors
> appreciate of hardware; same as with relational SQL?
> The fun thing is that the application doesn't seem to care much about
> extra RAM. Even when the SQL server sucks up 1.5GB versus 500MB, I still
> don't see any performance improvements noteworthy.
> Almost seems like there's some hardwired cap on max cursor performance

.
> --
> I doubt, therefore I might be.
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e50F7YSvFHA.3580@.TK2MSFTNGP10.phx.gbl

> Depending on how they are configured and such they may use a lot of
> Tempdb. So making sure tempdb is on a fast drive array might help.
> They tend to use a fair amount of cpu as well. But there is no magic
> switch to make cursors go faster. The real answer is sadly to
> rewrite them to not use cursors.
I tried "performance monitor" the tempdb while pounding it with nasty cursors
through the application. However I don't seen any kind of activity in the
tempdb. Looking at the database the application uses, I get a "log byte
flushed/sec" activity (about 61kb) every 4-5 second or so.
Monitoring CPU usage I get around ~60% on a hyperthread Intel P4. "Monitoring
Avg. Disk Transfer" I get numbers far below the capacity of the disk (it's a WDC
Raptor 10k rpm).
So I'm not really any closer figuring out where the bottleneck is regarding
cursor abuse.
I doubt, therefore I might be.

Optimizing cursor performance?

Hi there!
I have an application that uses cursors en massé in SQL. Actually, it does not
speak one word relational SQL with MSSQL, only cursors. I think it has a fair
bet in becoming World Champion in cursor abuse.
Anyway, the application is almost pure sequential/flat file based, and thus I
wondered, is there anything I can do to MSSQL that pulls out every bit of cursor
performance available, as it is very much needed?
Microsoft knows the problems, as they themself bought this very application..
--
I doubt, therefore I might be.Kim
Cursors are never (mostly) good choice. Without seeing your data ,it's hard
to suggest something.
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en massé in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||I have found in many cases that declaring the cursor as Static can often
lead to increased performance but as always it depends<g>.
--
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en massé in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ov$xEZNvFHA.3864@.TK2MSFTNGP12.phx.gbl
> Cursors are never (mostly) good choice. Without seeing your data
> ,it's hard to suggest something.
I know, that's why I'm trying to be slightly sarcastic in my message :).
What do you need of information? Unfortunately I can only pull out limited
information on this, as the application is closed source.
--
I doubt, therefore I might be.|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> I have found in many cases that declaring the cursor as Static can
> often lead to increased performance but as always it depends<g>.
But is there anything more 'high level' I can do? What does cursors appreciate
of hardware; same as with relational SQL?
The fun thing is that the application doesn't seem to care much about extra RAM.
Even when the SQL server sucks up 1.5GB versus 500MB, I still don't see any
performance improvements noteworthy.
Almost seems like there's some hardwired cap on max cursor performance :).
--
I doubt, therefore I might be.|||Depending on how they are configured and such they may use a lot of Tempdb.
So making sure tempdb is on a fast drive array might help. They tend to use
a fair amount of cpu as well. But there is no magic switch to make cursors
go faster. The real answer is sadly to rewrite them to not use cursors.
--
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:udfAjDSvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
>> I have found in many cases that declaring the cursor as Static can
>> often lead to increased performance but as always it depends<g>.
> But is there anything more 'high level' I can do? What does cursors
> appreciate of hardware; same as with relational SQL?
> The fun thing is that the application doesn't seem to care much about
> extra RAM. Even when the SQL server sucks up 1.5GB versus 500MB, I still
> don't see any performance improvements noteworthy.
> Almost seems like there's some hardwired cap on max cursor performance :).
> --
> I doubt, therefore I might be.
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e50F7YSvFHA.3580@.TK2MSFTNGP10.phx.gbl
> Depending on how they are configured and such they may use a lot of
> Tempdb. So making sure tempdb is on a fast drive array might help.
> They tend to use a fair amount of cpu as well. But there is no magic
> switch to make cursors go faster. The real answer is sadly to
> rewrite them to not use cursors.
I tried "performance monitor" the tempdb while pounding it with nasty cursors
through the application. However I don't seen any kind of activity in the
tempdb. Looking at the database the application uses, I get a "log byte
flushed/sec" activity (about 61kb) every 4-5 second or so.
Monitoring CPU usage I get around ~60% on a hyperthread Intel P4. "Monitoring
Avg. Disk Transfer" I get numbers far below the capacity of the disk (it's a WDC
Raptor 10k rpm).
So I'm not really any closer figuring out where the bottleneck is regarding
cursor abuse.
--
I doubt, therefore I might be.

Optimizing cursor performance?

Hi there!
I have an application that uses cursors en mass in SQL. Actually, it does n
ot
speak one word relational SQL with MSSQL, only cursors. I think it has a fai
r
bet in becoming World Champion in cursor abuse.
Anyway, the application is almost pure sequential/flat file based, and thus
I
wondered, is there anything I can do to MSSQL that pulls out every bit of cu
rsor
performance available, as it is very much needed?
Microsoft knows the problems, as they themself bought this very application.
.
I doubt, therefore I might be.Kim
Cursors are never (mostly) good choice. Without seeing your data ,it's hard
to suggest something.
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||I have found in many cases that declaring the cursor as Static can often
lead to increased performance but as always it depends<g>.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ov$xEZNvFHA.3864@.TK2MSFTNGP12.phx.gbl

> Cursors are never (mostly) good choice. Without seeing your data
> ,it's hard to suggest something.
I know, that's why I'm trying to be slightly sarcastic in my message

.
What do you need of information? Unfortunately I can only pull out limited
information on this, as the application is closed source.
I doubt, therefore I might be.|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> I have found in many cases that declaring the cursor as Static can
> often lead to increased performance but as always it depends<g>.
But is there anything more 'high level' I can do? What does cursors apprecia
te
of hardware; same as with relational SQL?
The fun thing is that the application doesn't seem to care much about extra
RAM.
Even when the SQL server sucks up 1.5GB versus 500MB, I still don't see any
performance improvements noteworthy.
Almost seems like there's some hardwired cap on max cursor performance

.
I doubt, therefore I might be.|||Depending on how they are configured and such they may use a lot of Tempdb.
So making sure tempdb is on a fast drive array might help. They tend to use
a fair amount of cpu as well. But there is no magic switch to make cursors
go faster. The real answer is sadly to rewrite them to not use cursors.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:udfAjDSvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> But is there anything more 'high level' I can do? What does cursors
> appreciate of hardware; same as with relational SQL?
> The fun thing is that the application doesn't seem to care much about
> extra RAM. Even when the SQL server sucks up 1.5GB versus 500MB, I still
> don't see any performance improvements noteworthy.
> Almost seems like there's some hardwired cap on max cursor performance

.
> --
> I doubt, therefore I might be.
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e50F7YSvFHA.3580@.TK2MSFTNGP10.phx.gbl

> Depending on how they are configured and such they may use a lot of
> Tempdb. So making sure tempdb is on a fast drive array might help.
> They tend to use a fair amount of cpu as well. But there is no magic
> switch to make cursors go faster. The real answer is sadly to
> rewrite them to not use cursors.
I tried "performance monitor" the tempdb while pounding it with nasty cursor
s
through the application. However I don't seen any kind of activity in the
tempdb. Looking at the database the application uses, I get a "log byte
flushed/sec" activity (about 61kb) every 4-5 second or so.
Monitoring CPU usage I get around ~60% on a hyperthread Intel P4. "Monitorin
g
Avg. Disk Transfer" I get numbers far below the capacity of the disk (it's a
WDC
Raptor 10k rpm).
So I'm not really any closer figuring out where the bottleneck is regarding
cursor abuse.
I doubt, therefore I might be.

optimizing bcp performance

I mistakenly posted this in the "programming" forum- reposting here as it
makes more sense to be in this forum...
We have a conversion process where we bcp out a table and then run a C++
program against it and then later bcp the data into a new table.
For a 24 Million row table, the bcp out takes about 83 minutes and the bcp
in takes about 87 minutes.
Is there any performance considerations to be aware of that could possibly
make this run faster (SQL 2000)? For example - I recall in SQL 6.5 that
there was a "select into/bulkcopy" option. Is there anything similar in SQL
2000?
For the bcp out, is there a way to reduce locking (maybe grab a table lock).
Any input would be appreciated.
Thanks in advanceyes,
Drop indexes on the table
change the database recovery mode to bulk logged ( In SQL Enterprise
Manager)
Do the bcp in (Use the TABLOCK hint)
Recreate the indexes
change back to full recovery
Do a transaction log backup..
You can also increase the load time by dividing the input file into multiple
files and firing off several concurrent BCPs. This is available when you use
the TABLOCK hint.
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:#7Ac88OuDHA.2308@.TK2MSFTNGP09.phx.gbl...
> I mistakenly posted this in the "programming" forum- reposting here as it
> makes more sense to be in this forum...
> We have a conversion process where we bcp out a table and then run a C++
> program against it and then later bcp the data into a new table.
> For a 24 Million row table, the bcp out takes about 83 minutes and the bcp
> in takes about 87 minutes.
> Is there any performance considerations to be aware of that could possibly
> make this run faster (SQL 2000)? For example - I recall in SQL 6.5 that
> there was a "select into/bulkcopy" option. Is there anything similar in
SQL
> 2000?
> For the bcp out, is there a way to reduce locking (maybe grab a table
lock).
> Any input would be appreciated.
> Thanks in advance
>

Monday, March 26, 2012

Optimizer goes bad but then recovers

I have a 10 gig database that is pretty consistent on performance.
But every few days some of the main stored procedures get a bad
execution plan which kills performance. It has happened while we have
had many users hitting the database and also when only a single person
is running.
The interesting part is that SQL Server has always corrected itself
after about 2 hours. We have not been able to repeat this in the test
lab. It only happens in production - lucky me.
Sometimes just doing a sp_updatestats will cure it for a while. One
time we changed a stored procedure to set ARITHABORT ON and the system
recovered immediately.
Any clue as to what might cause the optimizer to go a miss and
likewise why does it always recover in 2 hours?
Thanks
Hardware / Software being used
ASP.NET C# application using SqlClient
SQL Server 2000 Standard Edition, SP3a
Full rebuild of indexes is performed nightly
Possible that a SQL Server is using a cached execution plan that was
optimimal for the previous user but not for the other users? Don't know
about the 2 hours duration. Might give DBCC FREEPROCCACHE a try next time.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.c om...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> Hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly
|||I would investigate whether it has to do with statistics. Perhaps auto-update of statistics kicks in
,does a not so good job, then kicks in after two hours and then does a good job again. Perhaps you
can see a trend whether this is happening in conjunction with some mass load, removal or update?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.c om...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> Hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly
|||And you can do the checking by running profiler... Trace auto update
statistics, and SP plan reuse..
If you have a particular sp in mind... TEST it using extreme values for
parameters, looking at the execute plan to see if any plan changes between
index seek, and index scan...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.c om...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> Hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly
|||I agree about your assumption re: the 'not optimal for previous user', but I
don't think I woudl recommend dbcc freeproccache on a busy production
server.
You could simple sp_recompile that one procedure in question...
If the procedure is not exectued all that often and the cost of a recompile
is low compared to the cost of a 'bad plan' you might simply create the proc
using the with recompile.
Or even better... yuo might research what set of paramaters require widely
divergent exection plans and then create two new procs. Have the current
proc call each of the child procs based on it's knowledge of which proc will
have the better plan.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23TIpE1ztEHA.1276@.TK2MSFTNGP12.phx.gbl...
> Possible that a SQL Server is using a cached execution plan that was
> optimimal for the previous user but not for the other users? Don't know
> about the 2 hours duration. Might give DBCC FREEPROCCACHE a try next
time.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
> news:ec64e251.0410201939.5b4324a2@.posting.google.c om...
>

Optimizer goes bad but then recovers

I have a 10 gig database that is pretty consistent on performance.
But every few days some of the main stored procedures get a bad
execution plan which kills performance. It has happened while we have
had many users hitting the database and also when only a single person
is running.
The interesting part is that SQL Server has always corrected itself
after about 2 hours. We have not been able to repeat this in the test
lab. It only happens in production - lucky me.
Sometimes just doing a sp_updatestats will cure it for a while. One
time we changed a stored procedure to set ARITHABORT ON and the system
recovered immediately.
Any clue as to what might cause the optimizer to go a miss and
likewise why does it always recover in 2 hours?
Thanks
Hardware / Software being used
ASP.NET C# application using SqlClient
SQL Server 2000 Standard Edition, SP3a
Full rebuild of indexes is performed nightlyPossible that a SQL Server is using a cached execution plan that was
optimimal for the previous user but not for the other users? Don't know
about the 2 hours duration. Might give DBCC FREEPROCCACHE a try next time.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.com...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly|||I would investigate whether it has to do with statistics. Perhaps auto-updat
e of statistics kicks in
,does a not so good job, then kicks in after two hours and then does a good
job again. Perhaps you
can see a trend whether this is happening in conjunction with some mass load
, removal or update?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.com...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly|||And you can do the checking by running profiler... Trace auto update
statistics, and SP plan reuse..
If you have a particular sp in mind... TEST it using extreme values for
parameters, looking at the execute plan to see if any plan changes between
index seek, and index scan...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.com...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly|||I agree about your assumption re: the 'not optimal for previous user', but I
don't think I woudl recommend dbcc freeproccache on a busy production
server.
You could simple sp_recompile that one procedure in question...
If the procedure is not exectued all that often and the cost of a recompile
is low compared to the cost of a 'bad plan' you might simply create the proc
using the with recompile.
Or even better... yuo might research what set of paramaters require widely
divergent exection plans and then create two new procs. Have the current
proc call each of the child procs based on it's knowledge of which proc will
have the better plan.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23TIpE1ztEHA.1276@.TK2MSFTNGP12.phx.gbl...
> Possible that a SQL Server is using a cached execution plan that was
> optimimal for the previous user but not for the other users? Don't know
> about the 2 hours duration. Might give DBCC FREEPROCCACHE a try next
time.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
> news:ec64e251.0410201939.5b4324a2@.posting.google.com...
>

Optimizer goes bad but then recovers

I have a 10 gig database that is pretty consistent on performance.
But every few days some of the main stored procedures get a bad
execution plan which kills performance. It has happened while we have
had many users hitting the database and also when only a single person
is running.
The interesting part is that SQL Server has always corrected itself
after about 2 hours. We have not been able to repeat this in the test
lab. It only happens in production - lucky me.
Sometimes just doing a sp_updatestats will cure it for a while. One
time we changed a stored procedure to set ARITHABORT ON and the system
recovered immediately.
Any clue as to what might cause the optimizer to go a miss and
likewise why does it always recover in 2 hours?
Thanks
Hardware / Software being used
ASP.NET C# application using SqlClient
SQL Server 2000 Standard Edition, SP3a
Full rebuild of indexes is performed nightlyPossible that a SQL Server is using a cached execution plan that was
optimimal for the previous user but not for the other users? Don't know
about the 2 hours duration. Might give DBCC FREEPROCCACHE a try next time.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.com...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> Hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly|||I would investigate whether it has to do with statistics. Perhaps auto-update of statistics kicks in
,does a not so good job, then kicks in after two hours and then does a good job again. Perhaps you
can see a trend whether this is happening in conjunction with some mass load, removal or update?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.com...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> Hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly|||And you can do the checking by running profiler... Trace auto update
statistics, and SP plan reuse..
If you have a particular sp in mind... TEST it using extreme values for
parameters, looking at the execute plan to see if any plan changes between
index seek, and index scan...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.com...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> Hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly|||I agree about your assumption re: the 'not optimal for previous user', but I
don't think I woudl recommend dbcc freeproccache on a busy production
server.
You could simple sp_recompile that one procedure in question...
If the procedure is not exectued all that often and the cost of a recompile
is low compared to the cost of a 'bad plan' you might simply create the proc
using the with recompile.
Or even better... yuo might research what set of paramaters require widely
divergent exection plans and then create two new procs. Have the current
proc call each of the child procs based on it's knowledge of which proc will
have the better plan.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23TIpE1ztEHA.1276@.TK2MSFTNGP12.phx.gbl...
> Possible that a SQL Server is using a cached execution plan that was
> optimimal for the previous user but not for the other users? Don't know
> about the 2 hours duration. Might give DBCC FREEPROCCACHE a try next
time.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
> news:ec64e251.0410201939.5b4324a2@.posting.google.com...
> > I have a 10 gig database that is pretty consistent on performance.
> > But every few days some of the main stored procedures get a bad
> > execution plan which kills performance. It has happened while we have
> > had many users hitting the database and also when only a single person
> > is running.
> >
> > The interesting part is that SQL Server has always corrected itself
> > after about 2 hours. We have not been able to repeat this in the test
> > lab. It only happens in production - lucky me.
> >
> > Sometimes just doing a sp_updatestats will cure it for a while. One
> > time we changed a stored procedure to set ARITHABORT ON and the system
> > recovered immediately.
> >
> > Any clue as to what might cause the optimizer to go a miss and
> > likewise why does it always recover in 2 hours?
> >
> > Thanks
> >
> > Hardware / Software being used
> > ASP.NET C# application using SqlClient
> > SQL Server 2000 Standard Edition, SP3a
> > Full rebuild of indexes is performed nightly
>sql

Optimized Qeries without using Sub-Correlated Queries

Hi,

I am used to writing Sub-Correlated queries within my main queries. Although they work fine but i have read alot that they have performance hits. Also, as with time our data has increased, a simple SELECT statement with a few Sub-Queries tends to run slower which may be between 10-15 seconds. Following will be a simple example of what i mostly do:

SELECT DISTINCT C.CusID, C.Name, C.Age,

(

SELECT SUM (Price)

FROM CusotmerOrder

WHERE CusID_fk = CO.CusID_fk

) Total_Order_Price,

(

SELECT SUM (Concession)

FROM CusotmerOrder

WHERE CusID_fk = CO.CusID_fk

) Total_Order_Concession,

(

SELECT SUM (Price) - SUM (Concession)

FROM CusotmerOrder

WHERE CusID_fk = CO.CusID_fk

) Total_Difference

FROM Customer C

INNER JOIN CustomerOrder CO

ON C.CusID = CO.CusID_fk

.....

WHERE (conditions...)

My question is what would be a better way to handle the above query? How can i write a better yet simple query with optimized performance. I would also mention that in some of my asp.net applications, i use inline queries assigned to SqlCommand Object. The reason i mention it that since these queries are written in some class files, how would we still accomplish what i have mentioned above.Kindly could anyQuery Guru guide me writing better queries. I shall be obliged...

SELECT CustomerID, SUM(Price), SUM(Concession), SUM(Price-Concession)

FROM CusttomerOrder

GROUP BY CustomerID

|||

You can also use the above as a derived table and join on it, like:

SELECT Customers.CustomerID, Customers.Name, Customers.Age, OrdersTotals.TotalPrice, OrdersTotals.TotalConcession, OrdersTotals.TotalDifference

FROM Customers

INNER JOIN (

SELECT CustomerID, SUM(Price) AS TotalPrice, SUM(Concession) AS TotalConcession, SUM(Price-Concession) AS TotalDifference

FROM Orders

) AS OrdersTotals

ON Customers.CustomerID = OrdersTotals.CustomerID

|||

Hi Valenumr,

Thanks alot man, wow, you made things look so easy. The queries i have been working with were really optimized and the time went down from 10 sec to 1 sec. Greate help man and I learnt a great new technique.

Valenumr, now I need one more improvement on the above query. What if I want to get the same query working but on a day by day basis? Actually I use this query to generate a weekly report but it takes a long time. Also I tried to use the same for a monthly report, but the time exceeded 5 minutes. How can I improve the following query (im using the date add function to add days to given date):

Declare @.DateStart DateTime

Set @.DateStart = '2006-05-01'

SELECT DISTINCT C.CusID, C.Name, C.Age,

(

SELECT SUM (Price)

FROM CusotmerOrder

WHERE CusID_fk = CO.CusID_fk

AND Shop_Date = dateadd(dd,0, @.DateStart)

) Customer_Sales_Day1,

(

SELECT SUM (Price)

FROM CusotmerOrder

WHERE CusID_fk = CO.CusID_fk

AND Shop_Date = dateadd(dd,1, @.DateStart)

) Customer_Sales_Day2,

.................. continued for seven days ..................

(

SELECT SUM (Price)

FROM CusotmerOrder

WHERE CusID_fk = CO.CusID_fk

AND Shop_Date = dateadd(dd,6, @.DateStart)

) Customer_Sales_Day7,

FROM Customer C ...

WHERE (condition)

I look forward to your great help, thanks a lot...

|||

I think you want something like this:

SELECT Customer.CustomerID, Customer.Name, Customer.Age, OrderSummary.Date, OrderSummary.TotalPriceFROM CustomersINNER JOIN ( SELECT CustomerID, SUM(Price) AS TotalPrice, Shop_Date AS Date FROM Orders GROUP BY CustomerID, Shop_Date HAVING Shop_Date BETWEEN Start_Date AND End_Date) AS OrderSummaryON Customers.CustomerID = OrdersSummary.CustomerID

|||

Hi Valenumr,

Thanks for this query once again. It really worked like a charm and taught me a new technique once again. If you dont mind helping a bit more, kindly can i ask for one more help? This query has worked absolutely fine without any problem for me. What if a record does not exists for any given date between the given dates. In that case, i would like to have a 0 for the sum. e.g. For a customer, if records exist for dates 15, 16, 17, 19, 20 and 21 May, 2006 and a record for 18th May does not exists, then i would want to inset a 0 for 18th May, 2006. i want the records to look like the following:

EmpID Name D1 D2 D3 D4 D5 D6 D7

1001 ABC 200 100 90 0 9 74 10

Once again thanks alot for all the help you have given. You help has been very valuable to me. Cheers...

|||

one more thing. i have observed that whenever i use the Group By clause, it asks me to include all the SELECT ColumnsNames in the Group By clause. the message is"Column 'ColumnName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". is there a way that i can include only the required columns in the Group By clause.

|||

What you are trying to do is a pivot (or cross-tab) query. I am not very familiar with doing that in SQL, but there is a feature in SQL 2005. It would look something like this.

SELECT *FROM (SELECT EmpID,Name, Price,CONVERT(nvarchar(32), OrderDate, 106)AS OrderDateFROM OrdersWHERE OrderDateBETWEEN N'01/01/2007'AND N'01/07/2007')AS OrdersPIVOT (SUM(Price)FOR OrderDateIN ( [01 Jan 2007], [02 Jan 2007], [03 Jan 2007], [04 Jan 2007], [05 Jan 2007], [06 Jan 2007], [07 Jan 2007] ))AS PivotTable

Anyhow, for the date, you most likely want to do some type of conversion, otherwise the column names will have the long date format. I just used 106 as an example format, which is dd MMM yyyy, which you should note is how the pivot columns are named in the FOR ... IN (column_list).

|||

Hi Valenumr,

Yes your absolutely right that this is a PIVOT Technique. Some1 else also pointed out the same technique in a different post. Infact i do have the PIVOT Technique (not the built in PIVOT function) working. Anyways, can you answer one final question (hope its the last :)). Why do we have to include all the columns from the SELECT clause in our Group By clause otherwise it gives the message "Column 'ColumnName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" or im mistaken in understanding this point.

Valenumn, thanx alot for your time and patience. If i could vote you to be a MVP, i would have :)

|||

Say you do something like "SELECT A, B, SUM(C)..."

It seems sort of silly from the point of view that A, B must be specified in the group by clause, since they are in the select list and you are using an aggregate function (SUM) in the select list. You would almost think that by default saying GROUP BY A, B is redundant.

Well, basically, the SUM(C) has to know what perform the aggregate sum over, and the GROUP BY A, B is like the distict set of A / B combinations in the record source (which you should see corresponds to your SELECT DISTINCT statements).

However, you could GROUP BY A, B, D, E, etc. event though D and E are not in the SELECT list. I guess it is probably easier from the SQL specification / implementation perspective to require the GROUP BY to have a complete specification, and that any columns in the select list that aren't aggregated exist in that specification.

It would be awkward to say "SELECT A, B, SUM(C)... GROUP BY D" and have that *imply" A and B in the group by list, because it isn't clear. Also, what if you say "SELECT SUM(C)...", so in this case, you must have *some* group by, or should the engine assume that if you don't, "GROUP BY C" is implied... well, then going back to the original example, should C be included in the GROUP BY list if none is specified? The results of GROUP BY A, B, C will be very different (usually) than GROUP BY A, B.

So, I guess there are just way too many cases to consider that would lack direct clarity if the spec was any different. The GROUP BY clause must be explicit.

Finally, Imagine this... "SELECT A, B, SUM(C)... GROUP BY D"... well, what does that mean exactly? how can you GROUP BY D when for every D you have more than one combination of A / B, so that doesn't really make sense (going back to the SELECT DISTINCT analogy).

Friday, March 30, 2012

Optimizing SQL Query performance

Optimizing SQL 2000 SP4 for large amount of memory

optimizing query performance

optimizing load performance using partitioned tables in 2005

optimizing load performance using partitioned tables in 2005

optimizing load performance using partitioned tables in 2005

Wednesday, March 28, 2012

Optimizing insert performance

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Optimizing cursor performance?

Optimizing cursor performance?

Optimizing cursor performance?

optimizing bcp performance

Monday, March 26, 2012

Optimizer goes bad but then recovers

Optimizer goes bad but then recovers

Optimizer goes bad but then recovers

Optimized Qeries without using Sub-Correlated Queries

OpenXML Identity Insert

Blog Archive

About Me