Performance difference between SQL 6.5 and SQL 2000 server

Hello,
I've migrated my database from SQL 6.5 to SQL 2000 server.
After migration, some stored procedures are slower in SQL 2000 server
than SQL 6.5 server. The difference in time is very high - sometimes
as high as twice or thrice the time taken in 6.5 environment. The
client once mentioned to us that cursors are slow in SQL 2000 server.
We need a quick solution for this problem. A vast majority of the
procedures create 4-5 temporary tables and do an insert or update on
the temporart tables. Then there are some select statements from the
temporary tables.
I've rebuilt the indexes, updated the statistics, created tempdb device
on a separate drive. But no use.
Any help will be greatly appreciated.
Ravi,
I would look at the settings for the database as well. If you have Auto
Close, Auto Shrink, Auto Create Stats and other settings checked that could
be some of your problem.
Chris
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1138108502.952649.250460@.z14g2000cwz.googlegr oups.com...
> Hello,
> I've migrated my database from SQL 6.5 to SQL 2000 server.
> After migration, some stored procedures are slower in SQL 2000 server
> than SQL 6.5 server. The difference in time is very high - sometimes
> as high as twice or thrice the time taken in 6.5 environment. The
> client once mentioned to us that cursors are slow in SQL 2000 server.
>
> We need a quick solution for this problem. A vast majority of the
> procedures create 4-5 temporary tables and do an insert or update on
> the temporart tables. Then there are some select statements from the
> temporary tables.
> I've rebuilt the indexes, updated the statistics, created tempdb device
> on a separate drive. But no use.
> Any help will be greatly appreciated.
>
|||Also ensure that in SQL 2000 Server:
Size of the Tempdb is large enough
Tempdb it is set to Autogrow by considerable size
There is sufficient Free Space in your application's database
Your application's DB is set to Autogrow by considerable size
Please let us know if this helped you.
"Chris Wood" wrote:

> Ravi,
> I would look at the settings for the database as well. If you have Auto
> Close, Auto Shrink, Auto Create Stats and other settings checked that could
> be some of your problem.
> Chris
> <Ravi.Meenakshi@.gmail.com> wrote in message
> news:1138108502.952649.250460@.z14g2000cwz.googlegr oups.com...
>
>
|||The settings of the tempdb is same in 6.5 and 2000. There's enough
space in the DB. We've even moved tempdb device on a different drive.
With all these, still the performance is very bad in 2000. The question
is, with all the other factors and configuration options remaining the
same between 6.5 and 2000, why is there such a big difference in
performance?
|||When we went from 6.5 to 7.0 we found that SQL needed bigger and faster
hardware. SQL 7's requirements are higher than 6.5. Did you migrate in place
or upgrade to a different server? SQL2000 is better than SQL 7 but has the
same sort of hardware requirements.
Chris
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1138196134.975962.278020@.g43g2000cwa.googlegr oups.com...
> The settings of the tempdb is same in 6.5 and 2000. There's enough
> space in the DB. We've even moved tempdb device on a different drive.
> With all these, still the performance is very bad in 2000. The question
> is, with all the other factors and configuration options remaining the
> same between 6.5 and 2000, why is there such a big difference in
> performance?
>
|||We've migrated to a different server, but the hardware configuration of
6.5 server and 2000 server are the same. Does this mean that SQL 2000
server needs better hardware than 6.5?
|||Can you post an example? Include the Inout & Output tables definitions
if you can.
-- Barry Young
|||What are the hardware specs for the 2 servers?
Chris
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1138343333.576107.101060@.o13g2000cwo.googlegr oups.com...
> We've migrated to a different server, but the hardware configuration of
> 6.5 server and 2000 server are the same. Does this mean that SQL 2000
> server needs better hardware than 6.5?
>
|||I got a few pointers, but not sure how to take this forward.
In all the slow procesures, 5-6 temporary tables are created. A lot of
insert, update and select operations are happening on the temporary
tables. When I ran the profiler to capture the SP:Recompile event I
found that it's happening quite frequently. I read an article in MS
site that for temporary tables SP is recompiled for every 6 rows of
updation to temp tables. Moreover, the SP is very big and internally
calls 3-4 procedures in a cursor loop. I believe all of this will
warrant re-compilation. My question is:
1. Is there a way to turn off re-compilation of procedures?
2. Is there a way to increase the threshold for tempdb data updation -
from 6 rows to 500 or more?
It appears as though the procedure invocation is taking more time in
2000 than 6.5
Could it be because of recompilation? Please see the following
articles:
http://support.microsoft.com/default...;en-us;Q243586
http://support.microsoft.com/default...;EN-US;q195565
http://msdn.microsoft.com/library/de...ompilation.asp
Any help / suggestions would be greatly appreciated.
|||If recompiles caused by Temporary Tables is in fact your problem, then
the last article that you reference has a number of suggestions to
address it:
-- Use Table vairables instead (my preference)
-- Use Temporary tables with [keep plan]
-- Use Temporary tables with [keepfixed plan]
-- Barry Young
sql

Performance difference between SQL 6.5 and SQL 2000 server

Hello,
I've migrated my database from SQL 6.5 to SQL 2000 server.
After migration, some stored procedures are slower in SQL 2000 server
than SQL 6.5 server. The difference in time is very high - sometimes
as high as twice or thrice the time taken in 6.5 environment. The
client once mentioned to us that cursors are slow in SQL 2000 server.
We need a quick solution for this problem. A vast majority of the
procedures create 4-5 temporary tables and do an insert or update on
the temporart tables. Then there are some select statements from the
temporary tables.
I've rebuilt the indexes, updated the statistics, created tempdb device
on a separate drive. But no use.
Any help will be greatly appreciated.Ravi,
I would look at the settings for the database as well. If you have Auto
Close, Auto Shrink, Auto Create Stats and other settings checked that could
be some of your problem.
Chris
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1138108502.952649.250460@.z14g2000cwz.googlegroups.com...
> Hello,
> I've migrated my database from SQL 6.5 to SQL 2000 server.
> After migration, some stored procedures are slower in SQL 2000 server
> than SQL 6.5 server. The difference in time is very high - sometimes
> as high as twice or thrice the time taken in 6.5 environment. The
> client once mentioned to us that cursors are slow in SQL 2000 server.
>
> We need a quick solution for this problem. A vast majority of the
> procedures create 4-5 temporary tables and do an insert or update on
> the temporart tables. Then there are some select statements from the
> temporary tables.
> I've rebuilt the indexes, updated the statistics, created tempdb device
> on a separate drive. But no use.
> Any help will be greatly appreciated.
>|||Also ensure that in SQL 2000 Server:
Size of the Tempdb is large enough
Tempdb it is set to Autogrow by considerable size
There is sufficient Free Space in your application's database
Your application's DB is set to Autogrow by considerable size
Please let us know if this helped you.
"Chris Wood" wrote:
> Ravi,
> I would look at the settings for the database as well. If you have Auto
> Close, Auto Shrink, Auto Create Stats and other settings checked that could
> be some of your problem.
> Chris
> <Ravi.Meenakshi@.gmail.com> wrote in message
> news:1138108502.952649.250460@.z14g2000cwz.googlegroups.com...
> > Hello,
> >
> > I've migrated my database from SQL 6.5 to SQL 2000 server.
> > After migration, some stored procedures are slower in SQL 2000 server
> > than SQL 6.5 server. The difference in time is very high - sometimes
> > as high as twice or thrice the time taken in 6.5 environment. The
> > client once mentioned to us that cursors are slow in SQL 2000 server.
> >
> >
> > We need a quick solution for this problem. A vast majority of the
> > procedures create 4-5 temporary tables and do an insert or update on
> > the temporart tables. Then there are some select statements from the
> > temporary tables.
> >
> > I've rebuilt the indexes, updated the statistics, created tempdb device
> > on a separate drive. But no use.
> >
> > Any help will be greatly appreciated.
> >
>
>|||The settings of the tempdb is same in 6.5 and 2000. There's enough
space in the DB. We've even moved tempdb device on a different drive.
With all these, still the performance is very bad in 2000. The question
is, with all the other factors and configuration options remaining the
same between 6.5 and 2000, why is there such a big difference in
performance?|||When we went from 6.5 to 7.0 we found that SQL needed bigger and faster
hardware. SQL 7's requirements are higher than 6.5. Did you migrate in place
or upgrade to a different server? SQL2000 is better than SQL 7 but has the
same sort of hardware requirements.
Chris
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1138196134.975962.278020@.g43g2000cwa.googlegroups.com...
> The settings of the tempdb is same in 6.5 and 2000. There's enough
> space in the DB. We've even moved tempdb device on a different drive.
> With all these, still the performance is very bad in 2000. The question
> is, with all the other factors and configuration options remaining the
> same between 6.5 and 2000, why is there such a big difference in
> performance?
>|||We've migrated to a different server, but the hardware configuration of
6.5 server and 2000 server are the same. Does this mean that SQL 2000
server needs better hardware than 6.5?|||Can you post an example? Include the Inout & Output tables definitions
if you can.
-- Barry Young|||What are the hardware specs for the 2 servers?
Chris
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1138343333.576107.101060@.o13g2000cwo.googlegroups.com...
> We've migrated to a different server, but the hardware configuration of
> 6.5 server and 2000 server are the same. Does this mean that SQL 2000
> server needs better hardware than 6.5?
>|||I got a few pointers, but not sure how to take this forward.
In all the slow procesures, 5-6 temporary tables are created. A lot of
insert, update and select operations are happening on the temporary
tables. When I ran the profiler to capture the SP:Recompile event I
found that it's happening quite frequently. I read an article in MS
site that for temporary tables SP is recompiled for every 6 rows of
updation to temp tables. Moreover, the SP is very big and internally
calls 3-4 procedures in a cursor loop. I believe all of this will
warrant re-compilation. My question is:
1. Is there a way to turn off re-compilation of procedures?
2. Is there a way to increase the threshold for tempdb data updation -
from 6 rows to 500 or more?
It appears as though the procedure invocation is taking more time in
2000 than 6.5
Could it be because of recompilation? Please see the following
articles:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243586
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q195565
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp
Any help / suggestions would be greatly appreciated.|||If recompiles caused by Temporary Tables is in fact your problem, then
the last article that you reference has a number of suggestions to
address it:
-- Use Table vairables instead (my preference)
-- Use Temporary tables with [keep plan]
-- Use Temporary tables with [keepfixed plan]
-- Barry Young

Performance difference between SQL 6.5 and SQL 2000 server

Hello,
I've migrated my database from SQL 6.5 to SQL 2000 server.
After migration, some stored procedures are slower in SQL 2000 server
than SQL 6.5 server. The difference in time is very high - sometimes
as high as twice or thrice the time taken in 6.5 environment. The
client once mentioned to us that cursors are slow in SQL 2000 server.
We need a quick solution for this problem. A vast majority of the
procedures create 4-5 temporary tables and do an insert or update on
the temporart tables. Then there are some select statements from the
temporary tables.
I've rebuilt the indexes, updated the statistics, created tempdb device
on a separate drive. But no use.
Any help will be greatly appreciated.Ravi,
I would look at the settings for the database as well. If you have Auto
Close, Auto Shrink, Auto Create Stats and other settings checked that could
be some of your problem.
Chris
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1138108502.952649.250460@.z14g2000cwz.googlegroups.com...
> Hello,
> I've migrated my database from SQL 6.5 to SQL 2000 server.
> After migration, some stored procedures are slower in SQL 2000 server
> than SQL 6.5 server. The difference in time is very high - sometimes
> as high as twice or thrice the time taken in 6.5 environment. The
> client once mentioned to us that cursors are slow in SQL 2000 server.
>
> We need a quick solution for this problem. A vast majority of the
> procedures create 4-5 temporary tables and do an insert or update on
> the temporart tables. Then there are some select statements from the
> temporary tables.
> I've rebuilt the indexes, updated the statistics, created tempdb device
> on a separate drive. But no use.
> Any help will be greatly appreciated.
>|||Also ensure that in SQL 2000 Server:
Size of the Tempdb is large enough
Tempdb it is set to Autogrow by considerable size
There is sufficient Free Space in your application's database
Your application's DB is set to Autogrow by considerable size
Please let us know if this helped you.
"Chris Wood" wrote:

> Ravi,
> I would look at the settings for the database as well. If you have Auto
> Close, Auto Shrink, Auto Create Stats and other settings checked that coul
d
> be some of your problem.
> Chris
> <Ravi.Meenakshi@.gmail.com> wrote in message
> news:1138108502.952649.250460@.z14g2000cwz.googlegroups.com...
>
>|||The settings of the tempdb is same in 6.5 and 2000. There's enough
space in the DB. We've even moved tempdb device on a different drive.
With all these, still the performance is very bad in 2000. The question
is, with all the other factors and configuration options remaining the
same between 6.5 and 2000, why is there such a big difference in
performance?|||When we went from 6.5 to 7.0 we found that SQL needed bigger and faster
hardware. SQL 7's requirements are higher than 6.5. Did you migrate in place
or upgrade to a different server? SQL2000 is better than SQL 7 but has the
same sort of hardware requirements.
Chris
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1138196134.975962.278020@.g43g2000cwa.googlegroups.com...
> The settings of the tempdb is same in 6.5 and 2000. There's enough
> space in the DB. We've even moved tempdb device on a different drive.
> With all these, still the performance is very bad in 2000. The question
> is, with all the other factors and configuration options remaining the
> same between 6.5 and 2000, why is there such a big difference in
> performance?
>|||We've migrated to a different server, but the hardware configuration of
6.5 server and 2000 server are the same. Does this mean that SQL 2000
server needs better hardware than 6.5?|||Can you post an example? Include the Inout & Output tables definitions
if you can.
-- Barry Young|||What are the hardware specs for the 2 servers?
Chris
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1138343333.576107.101060@.o13g2000cwo.googlegroups.com...
> We've migrated to a different server, but the hardware configuration of
> 6.5 server and 2000 server are the same. Does this mean that SQL 2000
> server needs better hardware than 6.5?
>|||I got a few pointers, but not sure how to take this forward.
In all the slow procesures, 5-6 temporary tables are created. A lot of
insert, update and select operations are happening on the temporary
tables. When I ran the profiler to capture the SP:Recompile event I
found that it's happening quite frequently. I read an article in MS
site that for temporary tables SP is recompiled for every 6 rows of
updation to temp tables. Moreover, the SP is very big and internally
calls 3-4 procedures in a cursor loop. I believe all of this will
warrant re-compilation. My question is:
1. Is there a way to turn off re-compilation of procedures?
2. Is there a way to increase the threshold for tempdb data updation -
from 6 rows to 500 or more?
It appears as though the procedure invocation is taking more time in
2000 than 6.5
Could it be because of recompilation? Please see the following
articles:
http://support.microsoft.com/defaul...b;en-us;Q243586
http://support.microsoft.com/defaul...b;EN-US;q195565
queryrecompilation.asp" target="_blank">http://msdn.microsoft.com/library/d...compilation.asp
Any help / suggestions would be greatly appreciated.|||If recompiles caused by Temporary Tables is in fact your problem, then
the last article that you reference has a number of suggestions to
address it:
-- Use Table vairables instead (my preference)
-- Use Temporary tables with [keep plan]
-- Use Temporary tables with [keepfixed plan]
-- Barry Young

performance difference between query analyzer and .NET code.

Dear Sir,
I used dynamic SQL on stored procedure to do the paging in my site.
I often hit a connection time out problem with that dynamic SQl when I call
it from .NET code.
However if it run it under query analyzer, everything works fine.
Who can explain this to me and what should I do in this case?
regards,
Guoqi Zheng
http://www.ureader.comWhen you say "everything works fine", do you also mean that the execution
time in Query Analyzer is within the connection timeout you have set in your
.Net application?
ML
http://milambda.blogspot.com/|||> I used dynamic SQL on stored procedure to do the paging in my site.
> I often hit a connection time out problem with that dynamic SQl when I
> call
> it from .NET code.
> However if it run it under query analyzer, everything works fine.
> Who can explain this to me and what should I do in this case?
If "everything works fine" still means it's slow, and extends beyond the
timeout setting that ML mentioned, then everything is not working fine.
One thing you might try is a different paging approach. There are probably
a handful of samples here that will be faster and safer than dynamic SQL:
http://www.aspfaq.com/2120

performance comparision of different connection types

Do you know some performance differences between reading data from a stored procedure using:

1. OLE DB Connection and OLE DB Source

2. ADO.NET Connection and DatareaderSource

Przemo

If you could do some testing and share it here I'm sure people would be intreested in reading it.

-Jamie

Performance clues

I have a web application that calls stored procedures that sometimes timeout
at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
I happened to have profiler running during one of these timeout episodes. The
duration was in the 30 second range for the stored procedures, but the reads
were very low (I did not trace for CPU or Writes). Performance Monitor on the
CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
96 %) and the Disk Queue length.
When you have high duration and low reads, does that indicate an area to
pursue.
Using SQL Server 2000, SP4, on Win 2003.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
cbrichards via droptable.com wrote:
> I have a web application that calls stored procedures that sometimes timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
> I happened to have profiler running during one of these timeout episodes. The
> duration was in the 30 second range for the stored procedures, but the reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
>
Some other process is blocking the one in question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Blocking. Is anyone using Enterprise Manager to work with the data?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>

Performance clues

I have a web application that calls stored procedures that sometimes timeout
at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
I happened to have profiler running during one of these timeout episodes. The
duration was in the 30 second range for the stored procedures, but the reads
were very low (I did not trace for CPU or Writes). Performance Monitor on the
CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
96 %) and the Disk Queue length.
When you have high duration and low reads, does that indicate an area to
pursue.
Using SQL Server 2000, SP4, on Win 2003.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1> When you have high duration and low reads, does that indicate an area to
> pursue.
Looks like a blocking situation to me...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
> I happened to have profiler running during one of these timeout episodes. The
> duration was in the 30 second range for the stored procedures, but the reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||cbrichards via SQLMonster.com wrote:
> I have a web application that calls stored procedures that sometimes timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
> I happened to have profiler running during one of these timeout episodes. The
> duration was in the 30 second range for the stored procedures, but the reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
>
Some other process is blocking the one in question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Blocking. Is anyone using Enterprise Manager to work with the data?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||As others have already said, blocking is one very common cause of this
behaviour but database file growth during proc execution is another (monitor
for this with the profiler's file growth events).
96% Buffer Cache Hit Ratio isn't actually very high - you could very easily
be experiencing memory problems with BCHR at 96%. It's worth having a look
at the BufferManager's Page Life Expectancy counter as well - it measures
how long buffered pages are surviving in cache before being forced out by
pressure from other memory consumers. If this number falls during long
execution of your proc, it could be that other factors are indirectly
influencing this problem
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>

Performance clues

I have a web application that calls stored procedures that sometimes timeout
at 30 seconds. We cannot do it at will, so it makes troubleshooting difficul
t.
I happened to have profiler running during one of these timeout episodes. Th
e
duration was in the 30 second range for the stored procedures, but the reads
were very low (I did not trace for CPU or Writes). Performance Monitor on th
e
CPU was within an acceptable range, as were the Buffer Cache Hit ratio (abou
t
96 %) and the Disk Queue length.
When you have high duration and low reads, does that indicate an area to
pursue.
Using SQL Server 2000, SP4, on Win 2003.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1> When you have high duration and low reads, does that indicate an area to
> pursue.
Looks like a blocking situation to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:68fe0ef849e6f@.uwe...[vbcol
=seagreen]
>I have a web application that calls stored procedures that sometimes timeou
t
> at 30 seconds. We cannot do it at will, so it makes troubleshooting diffic
ult.
> I happened to have profiler running during one of these timeout episodes.
The
> duration was in the 30 second range for the stored procedures, but the rea
ds
> were very low (I did not trace for CPU or Writes). Performance Monitor on
the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (ab
out
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>[/vbcol]|||cbrichards via droptable.com wrote:
> I have a web application that calls stored procedures that sometimes timeo
ut
> at 30 seconds. We cannot do it at will, so it makes troubleshooting diffic
ult.
> I happened to have profiler running during one of these timeout episodes.
The
> duration was in the 30 second range for the stored procedures, but the rea
ds
> were very low (I did not trace for CPU or Writes). Performance Monitor on
the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (ab
out
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
>
Some other process is blocking the one in question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Blocking. Is anyone using Enterprise Manager to work with the data?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||As others have already said, blocking is one very common cause of this
behaviour but database file growth during proc execution is another (monitor
for this with the profiler's file growth events).
96% Buffer Cache Hit Ratio isn't actually very high - you could very easily
be experiencing memory problems with BCHR at 96%. It's worth having a look
at the BufferManager's Page Life Expectancy counter as well - it measures
how long buffered pages are surviving in cache before being forced out by
pressure from other memory consumers. If this number falls during long
execution of your proc, it could be that other factors are indirectly
influencing this problem
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>

Performance Anomaly

I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in 19
seconds. What can cause this severe difference in the performance of this
stored procedure?
When you are running the procedure from your application, make sure there's
no blocking happening in the server. You could use sp_who to check this.
Also, some of the SET options play a role in the query plan, and it could be
that these options are different between Query Analyzer and your application
connection.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in
19
seconds. What can cause this severe difference in the performance of this
stored procedure?
|||I have seen no blocking or a difference in the connection set options. Are
there any other possiblities? In the event I see blocking what should I do?
What does this indicate, is the server overloaded?
"Narayana Vyas Kondreddi" wrote:

> When you are running the procedure from your application, make sure there's
> no blocking happening in the server. You could use sp_who to check this.
> Also, some of the SET options play a role in the query plan, and it could be
> that these options are different between Query Analyzer and your application
> connection.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
> news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
> I am currently having a strange performance problem with one of my stored
> procedures. If I run the stored procedure from my web application or from
> Visual Studio it will timeout after 5 minutes (my timeout setting). If run
> the stored procedure using SQL Query Analyzer it executes without issue in
> 19
> seconds. What can cause this severe difference in the performance of this
> stored procedure?
>
>

Performance Anomaly

I am currently having a strange performance problem with one of my queries.
If I run a stored procedure from my web application or from Visual Studio it
will timeout after 5 minutes (my timeout setting). If run the stored
procedure using SQL Query Analyzer it executes without issue in 19 seconds.
What can cause this severe difference in the performance of this stored
procedure?Run a Profiler trace and see exactly how the procedure is being called
from your web application.|||Please do not multipost. I answered in .server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <TomMetrinex@.discussions.microsoft.com> wrote in message
news:5286E608-C003-4F01-B2AF-7913134C2996@.microsoft.com...
I am currently having a strange performance problem with one of my queries.
If I run a stored procedure from my web application or from Visual Studio it
will timeout after 5 minutes (my timeout setting). If run the stored
procedure using SQL Query Analyzer it executes without issue in 19 seconds.
What can cause this severe difference in the performance of this stored
procedure?

Performance Anomaly

I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in 1
9
seconds. What can cause this severe difference in the performance of this
stored procedure?When you are running the procedure from your application, make sure there's
no blocking happening in the server. You could use sp_who to check this.
Also, some of the SET options play a role in the query plan, and it could be
that these options are different between Query Analyzer and your application
connection.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in
19
seconds. What can cause this severe difference in the performance of this
stored procedure?|||I have seen no blocking or a difference in the connection set options. Are
there any other possiblities? In the event I see blocking what should I do?
What does this indicate, is the server overloaded?
"Narayana Vyas Kondreddi" wrote:

> When you are running the procedure from your application, make sure there'
s
> no blocking happening in the server. You could use sp_who to check this.
> Also, some of the SET options play a role in the query plan, and it could
be
> that these options are different between Query Analyzer and your applicati
on
> connection.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in messa
ge
> news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
> I am currently having a strange performance problem with one of my stored
> procedures. If I run the stored procedure from my web application or from
> Visual Studio it will timeout after 5 minutes (my timeout setting). If run
> the stored procedure using SQL Query Analyzer it executes without issue in
> 19
> seconds. What can cause this severe difference in the performance of this
> stored procedure?
>
>

Performance Anomaly

I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in 19
seconds. What can cause this severe difference in the performance of this
stored procedure?When you are running the procedure from your application, make sure there's
no blocking happening in the server. You could use sp_who to check this.
Also, some of the SET options play a role in the query plan, and it could be
that these options are different between Query Analyzer and your application
connection.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in
19
seconds. What can cause this severe difference in the performance of this
stored procedure?|||I have seen no blocking or a difference in the connection set options. Are
there any other possiblities? In the event I see blocking what should I do?
What does this indicate, is the server overloaded?
"Narayana Vyas Kondreddi" wrote:
> When you are running the procedure from your application, make sure there's
> no blocking happening in the server. You could use sp_who to check this.
> Also, some of the SET options play a role in the query plan, and it could be
> that these options are different between Query Analyzer and your application
> connection.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
> news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
> I am currently having a strange performance problem with one of my stored
> procedures. If I run the stored procedure from my web application or from
> Visual Studio it will timeout after 5 minutes (my timeout setting). If run
> the stored procedure using SQL Query Analyzer it executes without issue in
> 19
> seconds. What can cause this severe difference in the performance of this
> stored procedure?
>
>

Performance and logs

Hi,

I'm using SQL Server Management Studio Express and I have made a website
that uses a database with stored procedures.

When running pages in my webpages it cost around 2 seconds to load each
page.
Thanks too long!! But where is the problem?

I like to see a list of all executed stored procedures with there execute
time (for each page).
In this way I can check if the problem is here.

How can I get a logfile like this?

Thanks!

Performance tuning part of SQL Server does not come with Express, the Tuning Advisor which replaced the Index tuning Wizard, PerfMon and Profiler. The last can tell you stored proc duration and other statistics. Try the link below and download the eval which is good for 180days but it will be a good idea to buy the no deployment developer edition because it cost only $33. Hope this helps.

http://www.microsoft.com/sql/downloads/trial-software.mspx

Performance and Execution Plan

Performance !!!

Hi!

I am trying to execute an stored procedured that have already been used in another server (the test server ) but it takes 25 hours and in the another server takes just 40 minutes. !!

I have reviewed the indexes, statistics an so on, I have exactly the same quantity of data but cannot explain why does it takes so long in the server that is supposed to be the better one.

When I show de Execution Plan in the Query Analyzer in one server I get:

- Select (0%)

- Compute scalar (0%)

- Remote Query (100 %)

When I executed in the another one I get:

- Select

- Compute Scalar

- Nested Loops Left Outer Join (80%)

- Merge Join / Right

- Remote Query

- Sort

- Hash Match Right …

Even if I make a simple select by a certain period, in one server takes so long and in the another one just a few minutes.

Coul anybody please help me?

Sure... How much memory is on the test server versus the "other" server? How much CPU? 32 bit for both of them? Same operating systems? More data?|||By looking at the execution plan, they don't look like the same query.|||Can you please post some sample queries? If it is distributed queries then it is possible that you are getting a plan that is retrieving large number of rows from remote data source. But this depends on the statisitcs on any local tables that you are joining with and the query. There are ways to force joins to happen on the remote side. But anyway, you need to give more details before I can suggest any solution.|||

There is a chance that on server A you are querying it's local tables

where on server B you are querying remote tables (on server A).

If this is the case, try not to commit huge transactions over the network.

Also the source code for the query and the exact location of the tables would help define the problem

|||

Thank you for all your answers, let me give a little more of detail to clarify.

In my real environment I have this:

I have the problematic stored procedure in one server (let me say "Server A") and this procedure gets
data from Itself and server B (the linked server).

Basically the procedure makes a select from remote tables into a temporary table with
just a "where" of dates and another inserts in local tables.

To simplify the case I extract just the main select I use and it is exactly the same:
takes hours and hours to get the results of the "select".
But this "select" when is executed in my Test environment takes just a few minutes. (I am talking of 25 Hours vs. 40 Minutes aprox.)

What I have already checked is to have the same indexes, same statistics created
in both servers of both environments (server A, B, X, Y)

I also may say, the databases that I have in my Real Environment are just a Restore of the Databases that I have in my Test Environment.

The Hardware...
Real Environment:
Server A: 4 GB RAM,
Server B: 4 GM RAM (The linked Server)

Test Environment:
Server X: 4 GM RAM
Server Y: 1 GB RAM (The linked Server)

The Query...

For this query I checked the next points after executed in both environments:

Results of Execution Plan:
The results between Test an Real are very different, because while in the Real Environment there are a lot of
Logical and Physical Operators that describe the steps followed.

In terms of Costs the most expensive is "Nested Loops/Left Outer Join" with
80% of total execution plan. (this step just does not appear when I use my test environment).

In the Test Environment the only steps that i can see in the execution plan are:

Select 0%,

Compute Scalar 0% and

Remote Query 100%|||

For starters, I want to suggest that you create a view on the linked server with the query and use it instead remotely. This will provide the best performance and the plan will be local to that server in most cases. This provides better maintainability also and more tuning options. Also, please make sure that you are running the same version + service pack of SQL Server in your environment for comparison purposes.

Having said this, since all of the tables in the query are remote tables I don't see why we should even be trying to do any operations locally. It is possible that your test queries are little bit different or you are using different parameters. I am assuming that you are comparing your SP call with the same parameters between the two environments.

|||

Well, the thing here is that the stored procedured that I have to use have more statements, some of them need to access local tables. The queries that i used in both environments are exactly the same and over the same quantity of data (just a copy of one into the another).

As the main cost that i have is in the extract of the stored procedure that I show here, I have also tried to make the select step by step. I mean adding table by table in order to find any reason for the excessive time spend. I have noticed that after adding the table that have an “inner join”the process gets worst. So please, if you have any other idea let me know.

Performance

we have application and customized it by using VBA
In VBA Code i run some queries witch summrize some information
we plan set the query in stored procedure then we execute it from VBA
insted of run query from VBA
so how this will effect in performance
Thank you for your help
> so how this will effect in performance
From the SQL Server 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=optimsql.chm::/odp_tun_1a_6x45.htm">
All well-designed Microsoft SQL ServerT 2000 applications should use stored
procedures. This is true whether or not the business logic of the
application is written into stored procedures. Even standard Transact-SQL
statements with no business logic component gain a performance advantage
when packaged as stored procedures with parameters. Transact-SQL statements
compiled into stored procedures can save a significant amount of processing
at execution time. For more information, see Stored Procedures.
Another advantage of stored procedures is that client execution requests use
the network more efficiently than equivalent Transact-SQL statements sent to
the server. For example, suppose an application needs to insert a large
binary value into an image data column. To send the data in an INSERT
statement, the application must convert the binary value to a character
string (doubling its size), and then send it to the server. The server then
converts the value back into a binary format for storage in the image
column. In contrast, the application can create a stored procedure of the
form:
CREATE PROCEDURE P(@.p1 image) AS INSERT T VALUES (@.p1)
When the client application requests an execution of procedure P, the image
parameter value will stay in binary format all the way to the server,
thereby saving processing time and network traffic.
SQL Server stored procedures can provide even greater performance gains when
they include business services logic because it moves the processing to the
data, rather than moving the data to the processing.
</Excerpt>
There are also security benefits over direct table access.
Stored procedures are a Best Practice but whether or not your application
will experience faster performance simply by switching from embedded queries
to procs depends on other factors. It is doubtful you will see any
noticeable improvement if you currently have performance problems due to
lack of tuning or poor application design.
Hope this helps.
Dan Guzman
SQL Server MVP
"Serri" <Serrihakmi@.yahoo.com> wrote in message
news:1141037915.122884.185280@.i39g2000cwa.googlegr oups.com...
> we have application and customized it by using VBA
> In VBA Code i run some queries witch summrize some information
> we plan set the query in stored procedure then we execute it from VBA
> insted of run query from VBA
> so how this will effect in performance
>
> Thank you for your help
>

Performance

we have application and customized it by using VBA
In VBA Code i run some queries witch summrize some information
we plan set the query in stored procedure then we execute it from VBA
insted of run query from VBA
so how this will effect in performance
Thank you for your help> so how this will effect in performance
From the SQL Server 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=optimsql.chm::/odp_tun_1a_6x45.htm">
All well-designed Microsoft SQL ServerT 2000 applications should use stored
procedures. This is true whether or not the business logic of the
application is written into stored procedures. Even standard Transact-SQL
statements with no business logic component gain a performance advantage
when packaged as stored procedures with parameters. Transact-SQL statements
compiled into stored procedures can save a significant amount of processing
at execution time. For more information, see Stored Procedures.
Another advantage of stored procedures is that client execution requests use
the network more efficiently than equivalent Transact-SQL statements sent to
the server. For example, suppose an application needs to insert a large
binary value into an image data column. To send the data in an INSERT
statement, the application must convert the binary value to a character
string (doubling its size), and then send it to the server. The server then
converts the value back into a binary format for storage in the image
column. In contrast, the application can create a stored procedure of the
form:
CREATE PROCEDURE P(@.p1 image) AS INSERT T VALUES (@.p1)
When the client application requests an execution of procedure P, the image
parameter value will stay in binary format all the way to the server,
thereby saving processing time and network traffic.
SQL Server stored procedures can provide even greater performance gains when
they include business services logic because it moves the processing to the
data, rather than moving the data to the processing.
</Excerpt>
There are also security benefits over direct table access.
Stored procedures are a Best Practice but whether or not your application
will experience faster performance simply by switching from embedded queries
to procs depends on other factors. It is doubtful you will see any
noticeable improvement if you currently have performance problems due to
lack of tuning or poor application design.
Hope this helps.
Dan Guzman
SQL Server MVP
"Serri" <Serrihakmi@.yahoo.com> wrote in message
news:1141037915.122884.185280@.i39g2000cwa.googlegroups.com...
> we have application and customized it by using VBA
> In VBA Code i run some queries witch summrize some information
> we plan set the query in stored procedure then we execute it from VBA
> insted of run query from VBA
> so how this will effect in performance
>
> Thank you for your help
>

Performance

we have application and customized it by using VBA
In VBA Code i run some queries witch summrize some information
we plan set the query in stored procedure then we execute it from VBA
insted of run query from VBA
so how this will effect in performance
Thank you for your help> so how this will effect in performance
From the SQL Server 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=optimsql.chm::/odp_tun_1a_6x45.htm">
All well-designed Microsoft® SQL ServerT 2000 applications should use stored
procedures. This is true whether or not the business logic of the
application is written into stored procedures. Even standard Transact-SQL
statements with no business logic component gain a performance advantage
when packaged as stored procedures with parameters. Transact-SQL statements
compiled into stored procedures can save a significant amount of processing
at execution time. For more information, see Stored Procedures.
Another advantage of stored procedures is that client execution requests use
the network more efficiently than equivalent Transact-SQL statements sent to
the server. For example, suppose an application needs to insert a large
binary value into an image data column. To send the data in an INSERT
statement, the application must convert the binary value to a character
string (doubling its size), and then send it to the server. The server then
converts the value back into a binary format for storage in the image
column. In contrast, the application can create a stored procedure of the
form:
CREATE PROCEDURE P(@.p1 image) AS INSERT T VALUES (@.p1)
When the client application requests an execution of procedure P, the image
parameter value will stay in binary format all the way to the server,
thereby saving processing time and network traffic.
SQL Server stored procedures can provide even greater performance gains when
they include business services logic because it moves the processing to the
data, rather than moving the data to the processing.
</Excerpt>
There are also security benefits over direct table access.
Stored procedures are a Best Practice but whether or not your application
will experience faster performance simply by switching from embedded queries
to procs depends on other factors. It is doubtful you will see any
noticeable improvement if you currently have performance problems due to
lack of tuning or poor application design.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Serri" <Serrihakmi@.yahoo.com> wrote in message
news:1141037915.122884.185280@.i39g2000cwa.googlegroups.com...
> we have application and customized it by using VBA
> In VBA Code i run some queries witch summrize some information
> we plan set the query in stored procedure then we execute it from VBA
> insted of run query from VBA
> so how this will effect in performance
>
> Thank you for your help
>

perform floating point addition in SQL stored procedure

Hi..
Is there any way to add the value of 4 column and at the same time print the result together with another column?

I have this stored procedure:

CREATE PROCEDURE sp_queuelist AS
BEGIN
DECLARE @.temp1 As Decimal
DECLARE @.temp2 As Decimal
DECLARE @.cash As Decimal
DECLARE @.cheque As Decimal
DECLARE @.card As Decimal
DECLARE @.nets As Decimal
DECLARE @.bill As Decimal
DECLARE @.company As Decimal

SELECT
@.cash=Cash,@.cheque=Cheque,@.card=Card,@.nets=Nets,
@.bill=Bill,@.company=Company
FROM QUEUE
ORDER BY QNo

SET @.temp1 = @.cash + @.cheque + @.card + @.nets
SET @.temp2 = @.bill +@.company

Select QNO,
PCNo,
PName,
@.temp1 As totalCash,
@.temp2 As totalContract,
Doctor

FROM QUEUE
ORDER BY QNo
END
GO

Basically I want to add 4 columns: cash+cheque+card+nets into totalCash and bill+company to totalContract.

All the 6 field types are decimal.

I want to calculate temp1 and temp2 and then select the rest of the column to be displayed in the datagrid.

However, this stored procedure gives me 2 problems:
1. It gives the rounding result of the addition of decimal number, not the decimal itself.
2. All the rows in datagrid display the same result for totalCash and totalContract, which is the total from the last row in the table.

I seldom use stored procedure.
Is there any way to solve this problem?
Any suggestion is most welcomed.
Thank you in advanced.
Sincerely

Agustina(1) you can try changing the decimal to float.

(2) in the design of your table, you can set the formula for the column as sum of the other 4 columns. that way you dont need to worry abt doing the addition. anytime you make any change in any of the columns, the computed column is automatically updated.
if you need more help in this approach, let me know.

HTH.

perform all the actions in one pass rather than take serveral call

You're going to spend far less time writing a stored procedure that creates
one account and iterating through your array in your client application
calling the stored proc.
"E B via webservertalk.com" wrote:

> My application has one web form where admin can add as many users as
> desired and at the end when he press SaveChanges I want to add all of them
> to database, There should be some way to pass arrays to stored procedure..
I
> am looking for it or something similar to this...
> I want to pass an array into a stored procedure so that it can perform all
> the actions in one pass rather than take serveral calls to the server.
> I'm working with (SQL Server, .NET)
> --
> Message posted via http://www.webservertalk.com
>Alien2_51 wrote:
> You're going to spend far less time writing a stored procedure that
> creates one account and iterating through your array in your client
> application calling the stored proc.
>
bs
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||to Bob Barrows [MVP] what does it mean ?
Message posted via http://www.webservertalk.com|||E B via webservertalk.com wrote:
> to Bob Barrows [MVP] what does it mean ?
Oh, I'm sorry. it's short for:
I don't believe that what you said is necessarily correct.
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Ok, however i think i solve the problem , i used the link:
http://www.sommarskog.se/arrays-in-sql.html
what do u think'
Message posted via http://www.webservertalk.com|||E B via webservertalk.com wrote:
> Ok, however i think i solve the problem , i used the link:
> http://www.sommarskog.se/arrays-in-sql.html
> what do u think'
Well, there are several options in that link. Hopefully you satisfied
yourself that you picked the optimum one for your situation.
BTW, my comment was not directed at you. it was directed at the comment that
looping through the array in the client and making multiple calls to a
procedure ... across processes ... would be quicker than making a single
call to a stored procedure.
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

perfomance related questions

Why would a stored procedure take longer to run on an OLTP environment
although %processor time doesnt seem to be pegged ( 30%), %disk time is
around 10% and pages/sec is 0. It takes around 10 secs to run everytime on
an average but runs faster on development boxes like around 1 sec. It has
the same exact execution plan on both environments. My question is what else
do i need to look into since it doesnt seem to be hardware bound here. Using
SQL 2000. CPU and reads in trace are also higher than in our dev
environment. but the perfmon for h/w looks decent or atleast not that bad .
Any other counters to look at or suggestionsDo you have the same amount of data on both servers? For example if you
have a cursor that runs through all the records in one table and in the
development database you have only 100 records but in the production server
you have 100000 records, then I'd exepect that the stored procedure would
take longer to run on the production server. Also if you have lots of
users that are working with the database in the production, you might have
locking problems, that slow the execution.
Adi
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eY8NNr7YDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Why would a stored procedure take longer to run on an OLTP environment
> although %processor time doesnt seem to be pegged ( 30%), %disk time is
> around 10% and pages/sec is 0. It takes around 10 secs to run everytime on
> an average but runs faster on development boxes like around 1 sec. It has
> the same exact execution plan on both environments. My question is what
else
> do i need to look into since it doesnt seem to be hardware bound here.
Using
> SQL 2000. CPU and reads in trace are also higher than in our dev
> environment. but the perfmon for h/w looks decent or atleast not that bad
.
> Any other counters to look at or suggestions
>
>|||perfmon cpu is the instantaneous cpu usage, profiler cpu
is the cumulative cpu for the query, regardless of how
long it took,
unless a query takes several cpu secs, and the difference
between profiler duration and cpu is not larger, you may
not even notice the cpu in perfmon
>--Original Message--
>Assuming the stored procedure was only selecting , why
would the CPU from
>perfmon do not show it as busy yet consume more time in
CPU in profiler to
>execute the sproc. There is no parallelism. There are
some temp tables
>..There are some nested joins.
>"joe chang" <jchang6@.yahoo.com> wrote in message
>news:000d01c36401$27d19f60$a101280a@.phx.gbl...
>> what specifically are the production and development
>> systems?
>> processor, frequency, cache, #of cpu, memory, # of
disks,
>> status of hyper-threading?
>> what is in the execution plan of your stored proc ?
>> joins - what type, loop, hash, merge, row count for each
>> table, is there a temp table involved? is a parallel
plan
>> involved? if so, disabled HT or try OPTION (MAXDOP 1)
>> sometimes the dev env is JBOD (no raid) the prod env is
>> raid5, with absolutely horrible write performance, so
>> anything that involves writes will performance much
worse
>> on the $40k production system than the $1k dev system
>> >--Original Message--
>> >Why would a stored procedure take longer to run on an
>> OLTP environment
>> >although %processor time doesnt seem to be pegged (
30%),
>> %disk time is
>> >around 10% and pages/sec is 0. It takes around 10 secs
to
>> run everytime on
>> >an average but runs faster on development boxes like
>> around 1 sec. It has
>> >the same exact execution plan on both environments. My
>> question is what else
>> >do i need to look into since it doesnt seem to be
>> hardware bound here. Using
>> >SQL 2000. CPU and reads in trace are also higher than
in
>> our dev
>> >environment. but the perfmon for h/w looks decent or
>> atleast not that bad .
>> >Any other counters to look at or suggestions
>> >
>> >
>> >
>> >.
>> >
>
>.
>