Optimizing table in mssql

Hi there,
Is there an optimize table command in mssql which will work the same way as "OPTIMIZE TABLE tablename" of mysql?

I have a php application that should work on both mysql and mssql. To do defragmentation, I am using the above command. Is there an equivalent in mssql?

Cheers,
CeliaI presume optimise table defragments the table and indexes? If not - please post what it means.

To defrag an index check out the following in BoL:
2000
- DBCC DBREINDEX
- DBCC INDEXDEFRAG
2005
- ALTER INDEX ... REORGANISE\ REBUILD

You will need to see which options are best for your environment.

optimizing stored procedures

hi all,
i'm working in a database, i'm not the creater of it, and was assigned to optimize the stored procedures running against this database.
can someone tell me what steps should i follow to check these SP's and to what should i look at and what can be done to optimise them.

thanks expertsIt might be a two line question but it is a massive subject. People make their careers out of this.

read stuf here:
http://www.sql-server-performance.com/
Learn to read execution plans.
Learn about SET STATISTICS IO
Learn to use profiler.
Read BoL (section on Optimising Database Performance).
Learn your database.
Learn about indexing strategies.|||First make sure all the critical joins are indexed. Then eliminate any cursors or loops.|||I usually I just ask the QA people what parts of the app sucks the will to live out of them and then I trace those screens to identify the really big pieces of suck ass code and I look at the execution plans of those queries. I like being reactive.|||exactly. the ONLY sure way to find where the perf problems are is to measure.

ask any dev where the most time is spent in their code, and 90% will give you the wrong answer. the other 10% are right only because they have profiled it. :)|||I would trac it all to a table, th query the damn thing for the longest running pieces of code...

I guess we could make you look like a hero, sinve it seems no one else knows what's going on.

How big is this company?

And how what's your experience level

And do you have SQL Server client tools installed

Do you know what books online is?

Do you know what Google is?|||I guess we could make you look like a hero
...
Do you know what Google is?Downgrading as you type.|||There are some good articles about perf tuning here:

http://www.sql-server-performance.com/articles_performance.asp
http://www.sql-server-performance.com/articles_audit.asp

in particular this one will show you how to use profiler for catching the biggest pigs in your database:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

and elsewhere on that site.

Be aware, however, sql is not the only process that can kill your perf. compiled code can be poorly written too. for compiled code you need to use a different profiling tool (I usually use perf studio in VS).|||I trace all the queries to a table, as Brett mentioned. Then query it for the total number of reads grouped by say the first 50 or 100 characters of the textdata field. I find it is usually better to tune the average running query that runs 1,000's of times per day than the one pig that runs once at midnight. With all the variations that the textdata field can have, getting that aggregate right is a challenge, though.|||...
Downgrading as you type.

Are you saying that you should assume something about someone's experience?|||Nope - but if you take a poster on a forum from not knowing what google is to being a hero dba then I will eat all my hats.

Anyway yes - the OP needs to identify the sprocs to tune and then needs to learn the skills to tune them. Or post them here of course.

Optimizing Store Procedure and Avoiding Cursors....

I'm trying to optimize an ETL batch procedure which takes an extremely long
time to run. I was using cursors for most part of the batch script on
different Store Procedures. Now I'm trying to remove the cursors and replace
them with alternative methods such as using temp tables.
I'm really puzzled as to why the cursor procedure runs much faster than
using temp tables... can anyone point out what is wrong to me?
Below is a snippet of 2 test SPs I've compared against.
TestTable holds 200000 records with 3 columns
1. Using Cursors (This procedure took 4 - 8 secs to run)
declare @.test as numeric
declare @.header as nvarchar(50)
declare @.body as nvarchar(50)
declare @.footer as nvarchar(50)
declare test_cursor cursor for select header, body, footer from testTable
order by header
open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
set @.test = 0
while @.@.fetch_status = 0
begin
set @.test = @.test + 1
fetch next from test_cursor into @.header, @.body, @.footer
end
select @.test
close test_cursor
deallocate test_cursor
2. Using Temp Tables (This procedure took > 1 min)
declare @.test as numeric
declare @.header as nvarchar(50)
declare @.body as nvarchar(50)
declare @.footer as nvarchar(50)
declare @.count int, @.loopcount int
select identity(int,1,1) id, header, body, footer into #temp from testtable
select @.count = @.@.rowcount
set @.loopcount = 1
while @.loopcount <= @.count
begin
select @.header = header, @.body = body, @.footer = footer from
#temp where id = @.loopcount
set @.loopcount = @.loopcount + 1
end
drop table #tempLooking at the code I think it maybe the the where clause in the tempdb,
rember that it has to search through all 200000 records whereas with the
cursor your going through them from start to finish, no searching.
Peter
"Happiness is nothing more than good health and a bad memory."
Albert Schweitzer
"Nestor" wrote:

> I'm trying to optimize an ETL batch procedure which takes an extremely lon
g
> time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and repla
ce
> them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from testtab
le
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>
>|||Both 1 and 2 are cursors! Looping through a temp table is just a cursor
in disguise. Unfortunately your sample code doesn't do anything useful
so it's impossible to suggest an alternative set-based solution without
using cursors. Your goal should be to avoid processing one row at a
time by ANY method.
If you need more help please post DDL, sample data INSERTs and show
your required end result.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hello Nestor,
Going through your code, I dont see any reason why the #temp based approach
should be any better
than the Cursor based approach (if it is supposed to be).
The #temp based approach, Loops anyway with a while Loop which is simillar
to Cursor based method.
Normally, when we say we have to get rid of the cursor, we mean to replace
the Cursor based approach
with a SET BASED APPROACH and your #temp based approach still does record by
record. And as Peter says
for each iteration a select is executed against the table.
Hope this helps.
Gopi
"Nestor" <n3570r@.yahoo.com> wrote in message
news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to optimize an ETL batch procedure which takes an extremely
> long time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and
> replace them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from
> testtable
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>|||Although I haven't tested it, I'd expect the pseudo-cursor would perform
much better if you add a unique clustered index on the #temp id column.
However, as David pointed out, changing a cursor to another iterative
technique isn't the ideal solution and might not be any faster than the
cursor.
We can't make any specific recommendations because the example you posted
does no productive work. If your actual process does something like execute
another proc for each row returned, the set-based solution would be to use
in-line set-based processing instead. This technique is often orders of
magnitude faster than an iterative approach.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to optimize an ETL batch procedure which takes an extremely
> long time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and
> replace them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from
> testtable
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>|||Dan,
Could you please share with me what "in-line set-based " means.
I was looking at a Stored Proc which has a nested cursors (One cursor inside
another cursor)
and the While loop of the second a SP is called with the two parameters
(each of them coming
from each of the Cursor code.
Here is the PSeudo-Code :
Declare Cursor Cursor1 Select * from table1
Open Cursor1
Fetch into @.var1
While @.@.FETCH_STATUS <> 1
Declare Cursor Cursor2 Select * from table2
Open Cursor2
Fetch into @.var2
While @.@.FETCH_STATUS <> 1
Exec Spname @.var1, @.var2
........
.....
DEALLOCATE Cursor1
DEALLOCATE Cursor2
I have noticed that the two cursors can be combined into one using
appropriate Joins.
However, I was under the impression I will still have to use a Cursor for
executing the SP
Could you please point me in the right direction if there is a way out.
Thanks,
Gopi
<< If your actual process does something like execute another proc for each
row returned, the set-based
solution would be to use in-line set-based processing instead>>
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OXOup8KJFHA.4092@.tk2msftngp13.phx.gbl...
> Although I haven't tested it, I'd expect the pseudo-cursor would perform
> much better if you add a unique clustered index on the #temp id column.
> However, as David pointed out, changing a cursor to another iterative
> technique isn't the ideal solution and might not be any faster than the
> cursor.
> We can't make any specific recommendations because the example you posted
> does no productive work. If your actual process does something like
> execute another proc for each row returned, the set-based solution would
> be to use in-line set-based processing instead. This technique is often
> orders of magnitude faster than an iterative approach.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||> However, I was under the impression I will still have to use a Cursor
for
> executing the SP
Yes, using an SP in this way forces you to use the iterative row-by-row
approach. The set-based solution is to perform whatever logic is
contained in SPname using a query against your tables so that the
entire operation is performed against the data set rather than one row
at a time. For data manipulation operations that usually has advantages
over the cursor in terms of performance, scalability and
maintainability.
David Portas
SQL Server MVP
--|||> Here is the PSeudo-Code :
Your pseudo-code effectively calls the proc for each row returned by the
Cartesian product of table1 and table2. Assuming that the executed proc
simply inserts into a third table, a working code example:
CREATE TABLE table1
(
Col1 int NOT NULL PRIMARY KEY
)
INSERT INTO table1 VALUES(1)
INSERT INTO table1 VALUES(2)
INSERT INTO table1 VALUES(3)
CREATE TABLE table2
(
Col1 int NOT NULL PRIMARY KEY
)
INSERT INTO table2 VALUES(4)
INSERT INTO table2 VALUES(5)
INSERT INTO table2 VALUES(6)
CREATE TABLE table3
(
Col1 int NOT NULL,
Col2 int NOT NULL,
PRIMARY KEY (Col1, Col2)
)
GO
CREATE PROC Spname
@.var1 int,
@.var2 int
AS
SET NOCOUNT ON
INSERT INTO Table3(Col1, Col2)
VALUES(@.var1, @.var2)
GO
DECLARE @.var1 int,
@.var2 int
DECLARE Cursor1 CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT * FROM table1
OPEN Cursor1
FETCH NEXT FROM Cursor1 INTO @.var1
WHILE @.@.FETCH_STATUS <> -1
BEGIN
DECLARE Cursor2 CURSOR LOCAL FOR SELECT * FROM table2
OPEN Cursor2
FETCH NEXT FROM Cursor2 INTO @.var2
WHILE @.@.FETCH_STATUS <> -1
BEGIN
EXEC Spname @.var1, @.var2
FETCH NEXT FROM Cursor2 INTO @.var2
END
CLOSE Cursor2
DEALLOCATE Cursor2
FETCH NEXT FROM Cursor1 INTO @.var1
END
CLOSE Cursor1
DEALLOCATE Cursor1
GO

> I have noticed that the two cursors can be combined into one using
> appropriate Joins.
Exactly. That's the first step.

> However, I was under the impression I will still have to use a Cursor for
> executing the SP
Yes you will. This is why I suggested including the equivalent proc code
inline *instead of* executing the proc for each row. The entire cursor
script and proc code in my functional example could be replaced with a
single insert statement:
INSERT INTO Table3(Col1, Col2)
SELECT t1.Col1, t2.Col1
FROM table1 t1
CROSS JOIN table2 t2
Of course, a lot depends on exactly what the called proc does. Also, your
actual code probably isn't a CROSS JOIN. A simple INNER JOIN example:
INSERT INTO Table3(Col1, Col2)
SELECT t1.Col1, t2.Col1
FROM table1 t1
JOIN table2 t2 ON
t1.Col1 = t2.SomeColumn
If you need more help, you need to provide details on what the proc actually
does. Actual code will help.
Hope this helps.
Dan Guzman
SQL Server MVP
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:%23Sx92CLJFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Dan,
> Could you please share with me what "in-line set-based " means.
> I was looking at a Stored Proc which has a nested cursors (One cursor
> inside another cursor)
> and the While loop of the second a SP is called with the two parameters
> (each of them coming
> from each of the Cursor code.
> Here is the PSeudo-Code :
> Declare Cursor Cursor1 Select * from table1
> Open Cursor1
> Fetch into @.var1
> While @.@.FETCH_STATUS <> 1
>
> Declare Cursor Cursor2 Select * from table2
> Open Cursor2
> Fetch into @.var2
> While @.@.FETCH_STATUS <> 1
> Exec Spname @.var1, @.var2
> ........
> .....
> DEALLOCATE Cursor1
> DEALLOCATE Cursor2
> I have noticed that the two cursors can be combined into one using
> appropriate Joins.
> However, I was under the impression I will still have to use a Cursor for
> executing the SP
> Could you please point me in the right direction if there is a way out.
> Thanks,
> Gopi
> << If your actual process does something like execute another proc for
> each row returned, the set-based
> solution would be to use in-line set-based processing instead>>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OXOup8KJFHA.4092@.tk2msftngp13.phx.gbl...
>|||Thanks for pointing that out. I've got the code base from an article in
http://www.extremeexperts.com/SQL/A...TSQLResult.aspx
Am I doing it wrong with instructions from that article? In any case, what
exactly is SET BASED APPROACH? I do realise and know that SQL Server is
meant to be relational and not sequential, but I need to understand more on
how to apply the alternative first before trying to overhaul the scripts
running in the production servers.
Can anyone give me an equivalent of the cursor procedure I posted using SET
BASE APPROACH so that I can better relate?
Thanks.
Regards,
Nestor
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:e$w%23rZJJFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Hello Nestor,
> Going through your code, I dont see any reason why the #temp based
> approach should be any better
> than the Cursor based approach (if it is supposed to be).
> The #temp based approach, Loops anyway with a while Loop which is simillar
> to Cursor based method.
> Normally, when we say we have to get rid of the cursor, we mean to replace
> the Cursor based approach
> with a SET BASED APPROACH and your #temp based approach still does record
> by record. And as Peter says
> for each iteration a select is executed against the table.
> Hope this helps.
> Gopi
>
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||The article demonstrates some techniques for iteration but doesn't give
any recommendations as to when to use them. 99.99% of the time
row-by-row processing isn't necessary so those techniques shouldn't be
required. The Set Based approach means that you use SQL DML statements
(basically SELECT, UPDATE, INSERT, DELETE) that operate on a SET of
rows at a time rather than ONE row at a time. That should always be the
standard, preferred way to write code.

> Can anyone give me an equivalent of the cursor procedure I posted
using SET
> BASE APPROACH so that I can better relate?
But your cursor didn't do anything except assign a few variables. How
can we show you how to replace it if we don't know what it's supposed
to do? What we need to know is what you are actually trying to achieve
then we can suggest the right solution. It may even be that there isn't
a set-based solution to your problem - that's rarely the case but we
can't tell you until we understand the problem.
The following article explains the best way to post your problem here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

Optimizing query with UDF and table vars and IN

Hi, I am trying to optimize this scenario.
I have a query that is returning a list of services. Each service is done
by an employee and for a client. Employees have rights to see only certain
sevices. They can see any service that is done by an employee they have
rights to OR done for a client they have rights to.
CREATE TABLE [Service] (
service_id Int IDENTITY(1,1) NOT NULL,
emp_id Int,
client_id Int)
I have 2 UDFs that return a list of emp_id's they have rights to and a list
of client_id's they have rights to respectively.
CREATE FUNCTION dbo.f_list_emps (@.my_emp_id Int)
RETURNS @.EmpList TABLE (emp_id int not null unique)
AS
BEGIN
// Fill @.EmpList here with a bunch of queries
END
CREATE FUNCTION dbo.f_list_clients (@.my_emp_id Int)
RETURNS @.ClientList TABLE (client_id int not null unique)
AS
BEGIN
// Fill @.ClientList here with a bunch of queries
END
The actual query is built dynamically because it can have about 15 different
parameters passed to it, but a simplified version would look like:
SELECT * FROM Service
WHERE emp_id IN
(SELECT emp_id FROM dbo.f_list_emps(@.my_emp_id))
OR client_id IN
(SELECT client_id FROM dbo.f_list_clients(@.my_emp_id))
I'm looking for a way to optimize this a bit better. I can't join the table
vars directly because of the 'OR', and I don't want to do a UNION of 2
queries each with a separate join because of all the other parameters
involved in the query.
Thanks for any advice,
DaveDavid D Webb (spivey@.nospam.post.com) writes:
> The actual query is built dynamically because it can have about 15
> different parameters passed to it, but a simplified version would look
> like: >
> SELECT * FROM Service
> WHERE emp_id IN
> (SELECT emp_id FROM dbo.f_list_emps(@.my_emp_id))
> OR client_id IN
> (SELECT client_id FROM dbo.f_list_clients(@.my_emp_id))
> I'm looking for a way to optimize this a bit better. I can't join the
> table vars directly because of the 'OR', and I don't want to do a UNION
> of 2 queries each with a separate join because of all the other
> parameters involved in the query.
It's of course impossible to suggest optimizations when I don't see
the tables, and do not the full query.
What I would consider is to insert the data from the table functions
into temp tables. Temp tables have statistics, and since you are running
a dynamic query anyway, you could just as well make use of that statistics.
If you use the UDFs in the query, SQL Server will make some standard
assumptions about what they return.
I would also consider running a UNION of two queries. If you are building
the query dynamically, it should not be much of an issue to repeat the
queries. But you should benchmark whether UNION actually gives an
improvement.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||A simplified query probably won't do.
But here a tip that might be useful: drop the UDF's. If you can rewrite
them as views, then the optimizer can properly optimize the query. When
using UDF's in this fashion you are bound to run into performance
problems as the resultset grows.
Gert-Jan
David D Webb wrote:
> Hi, I am trying to optimize this scenario.
> I have a query that is returning a list of services. Each service is done
> by an employee and for a client. Employees have rights to see only certai
n
> sevices. They can see any service that is done by an employee they have
> rights to OR done for a client they have rights to.
> CREATE TABLE [Service] (
> service_id Int IDENTITY(1,1) NOT NULL,
> emp_id Int,
> client_id Int)
> I have 2 UDFs that return a list of emp_id's they have rights to and a lis
t
> of client_id's they have rights to respectively.
> CREATE FUNCTION dbo.f_list_emps (@.my_emp_id Int)
> RETURNS @.EmpList TABLE (emp_id int not null unique)
> AS
> BEGIN
> // Fill @.EmpList here with a bunch of queries
> END
> CREATE FUNCTION dbo.f_list_clients (@.my_emp_id Int)
> RETURNS @.ClientList TABLE (client_id int not null unique)
> AS
> BEGIN
> // Fill @.ClientList here with a bunch of queries
> END
> The actual query is built dynamically because it can have about 15 differe
nt
> parameters passed to it, but a simplified version would look like:
> SELECT * FROM Service
> WHERE emp_id IN
> (SELECT emp_id FROM dbo.f_list_emps(@.my_emp_id))
> OR client_id IN
> (SELECT client_id FROM dbo.f_list_clients(@.my_emp_id))
> I'm looking for a way to optimize this a bit better. I can't join the tab
le
> vars directly because of the 'OR', and I don't want to do a UNION of 2
> queries each with a separate join because of all the other parameters
> involved in the query.
> Thanks for any advice,
> Dave

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 MS OLAP Cube build time and response time

Hi,
Can someone highlight some of the common methods to improve and optimize the CUBE BUILD TIME and RESPONSE TIME using MS OLAP Services with SQL Server?
Regards,
Omkarwhat kindof dataset are you trying to load?

never use ROLAP, unless you have to.

do analysis services on a dedicated server.

www.sqlserverperformance.com

a lot of good tips are there..

Optimizing connection pooling

Hi,

I'm trying to optimize an ASP.NET 2.0 app where, with a load of 100 users, the CPU usage on the web server is a reasonable <20% but the db server (SQL Server 2005) often goes up to 100% and never below 20%. When viewing the Performance Monitor counter SQL Server :General Statistics/User Connections this shows that there are 50-65 connections. When the number of connections go up the db server's CPU usage also goes up along with web page response times.

When viewing the Sql server Activity Monitor it, for all connections, always says that the Login Time and the Last Batch are the same. This has got me wondering if no connections are pooled and that all connections always are closed and reopened and that would explain why the db server is so jammed.

Could someone give me some pointers as what to check to find optimization points.

Regards,

Mathias

Which operating system is running on your server?|||

Hi Mathias,

Please check in your connection string to see if pooling has been turned off.

If it is on, you can try to increase the connection lifetime and the min pool size, to make sure that the connections are available for using.

sql

Optimizing a query

Hello,
I am hoping someone here can help me optimize the following query:
SELECT
INCOMING.DATE_TIME, INCOMING.URL, INCOMING.HITS,
USER_NAMES.USER_LOGIN_NAME,
CATEGORY.NAME
FROM
(wsHQMay2004.dbo.INCOMING INCOMING INNER JOIN wsHQMay2004.dbo.CATEGORY CATEGORY ON INCOMING.CATEGORY = CATEGORY.CATEGORY)
INNER JOIN wsHQMay2004.dbo.USER_NAMES USER_NAMES ON INCOMING.USER_ID = USER_NAMES.USER_ID
WHERE
INCOMING.DATE_TIME >= '2004-05-01 00:00:00.00' AND
INCOMING.DATE_TIME < '2004-06-01 00:00:00.00'
ORDER BY
INCOMING.URL ASC

I am just hoping to get some tips on perhaps a better way to write this query as right now, due to the size of the incoming table, this query just takes forever.

Any advise will be apreciated.

Thanks.I would recommend you to create clustered index on INCOMING.DATE_TIME if you do not have it. Think about indexes for CATEGORY.CATEGORY and USER_NAMES.USER_ID. Check execution plan for this query - may be it will be good idea to use INNER LOOP JOIN (it depends how many records are in tables CATEGORY and USER_NAMES).

Try this version:

SELECT INCOMING.DATE_TIME, INCOMING.URL, INCOMING.HITS,
,(select USER_LOGIN_NAME from wsHQMay2004.dbo.USER_NAMES where USER_NAMES.USER_ID=INCOMING.USER_ID) as 'USER_LOGIN_NAME'
,(select NAME from wsHQMay2004.dbo.CATEGORY where CATEGORY.CATEGORY=INCOMING.CATEGORY) as 'NAME'
FROM wsHQMay2004.dbo.INCOMING INCOMING
WHERE
INCOMING.DATE_TIME >= '2004-05-01 00:00:00.00' AND
INCOMING.DATE_TIME < '2004-06-01 00:00:00.00'
ORDER BY INCOMING.URL ASC|||I currently have a non-clustered index on the date_time field. Perhaps I will try a clustered one. The incoming table is about 65 Million records.

I am not very familiar with SQL Server (Oracle is mainly what I use), how do I get the execution plan for a query?|||Query analyzer - put query in, then menu: Query- Display Estimated Execution Plan or Show Execution Plan. You can make a screenshot of execution plan and somebody could help you to improve query performance.|||I hope you have indexes on the USER_ID and CATEGORY fields. I'd prefer a clustered index on those.

You MAY get better performance by creating a covered composite index on DATE_TIME and USER_ID. Worth a shot...|||I am not very familiar with SQL Server (Oracle is mainly what I use), how do I get the execution plan for a query?I'd recommend using SET SHOWPLAN_TEXT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_93sk.asp) for an Oracle user. The text output isn't as pretty as the GUI is, but it makes more sense if you are familiar with query plans and is generally more familiar to an Oracle user.

-PatPsql

Optimizing a query

Hi
I have a query which I would like to optimize and thought I had found a
solution.
The query is a join of several selects from several different tables.
So we have in general the following:
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
JOIN
SELECT table1.Col1 FROM table2 WHERE exp2
ON table1.Col1 = table2.Col1
)
or something like that. Of course my query was a lot larger than this,
but it is just to illustrate the problem.
The above was too slow, and I made an attempt to optimize it and
thought it did work. The following is what I did:
Instead of the SELECT's I could create temporary tables and select into
those so I get the following:
SELECT table1.Col1
INTO #temp_table1
FROM table1 WHERE exp1
SELECT table1.Col1
INTO #temp_table2
FROM table2 WHERE exp2
And then join those two tables:
SELECT Col1 FROM
(
#temp_table1
JOIN
#temp_table2
ON #temp_table1.Col1 = #temp_table2.Col1
)
and at the end drop the temporary tables.
DROP Table #temp_table1
DROP Table #temp_table2
This gives the same result as the original statement.
I profiled a bit and saw that if I created the temp tables, and ran the
join statement several times after each other, the first time it is run
it take a lot of time compared to the following. Can anyone explain to
me why this is the case? I thought that the temporary tables were
created and loaded into the cache, and therefore all calls to the big
JOIN statement should take equally long time.
The thing is that I cannot reuse the temporary tables, so I get no
benefit from all the above.
Thank you very much in advance
JeejiHi
First of all the first script throws a syntax error. Is that MySQL syntax?
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 JOIN table2 ON table1.Col1 =table2.Col1 WHERE exp1 AND ....
) AS Der
--Or
SELECT * FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
UNION ALL
SELECT table1.Col1 FROM table2 WHERE exp2
) AS Der
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Uri,
I think that is just a small fragment of the query. (At least I hope it's
just a fragment...)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Hi.
I thought I made it clear that it was just a fragment of the code. But
now something new came to my attention.
I can see that if I clear the SQL server cache and call the first SQL
statement (the one without the temporary table), the SQL profiler shows
that this call takes longer (around twice) than it takes if I clear the
server cache and run the temporary table version of the call.
However if I do the same calls from a C# application, the opposite
occurs.
To clear the SQL server cache I use:
DBCC FreeSystemCache('All')
And for the C# application I am using .Net 2.0, using the
System.Data.SQLClient calls.
Any advice would just be great.
Arnie Rowland wrote:
> Uri,
> I think that is just a small fragment of the query. (At least I hope it's
> just a fragment...)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "jeeji" <jihad_dk@.yahoo.com> wrote in message
> news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> > Hi
> >
> > I have a query which I would like to optimize and thought I had found a
> > solution.
> >
> > The query is a join of several selects from several different tables.
> >
> > So we have in general the following:
> >
> > SELECT Col1 FROM
> > (
> > SELECT table1.Col1 FROM table1 WHERE exp1
> > JOIN
> > SELECT table1.Col1 FROM table2 WHERE exp2
> > ON table1.Col1 = table2.Col1
> > )
> >
> > or something like that. Of course my query was a lot larger than this,
> > but it is just to illustrate the problem.
> >
> > The above was too slow, and I made an attempt to optimize it and
> > thought it did work. The following is what I did:
> > Instead of the SELECT's I could create temporary tables and select into
> > those so I get the following:
> >
> > SELECT table1.Col1
> > INTO #temp_table1
> > FROM table1 WHERE exp1
> >
> > SELECT table1.Col1
> > INTO #temp_table2
> > FROM table2 WHERE exp2
> >
> > And then join those two tables:
> >
> > SELECT Col1 FROM
> > (
> > #temp_table1
> > JOIN
> > #temp_table2
> > ON #temp_table1.Col1 = #temp_table2.Col1
> > )
> >
> > and at the end drop the temporary tables.
> >
> > DROP Table #temp_table1
> > DROP Table #temp_table2
> >
> > This gives the same result as the original statement.
> >
> > I profiled a bit and saw that if I created the temp tables, and ran the
> > join statement several times after each other, the first time it is run
> > it take a lot of time compared to the following. Can anyone explain to
> > me why this is the case? I thought that the temporary tables were
> > created and loaded into the cache, and therefore all calls to the big
> > JOIN statement should take equally long time.
> > The thing is that I cannot reuse the temporary tables, so I get no
> > benefit from all the above.
> >
> > Thank you very much in advance
> > Jeeji
> >|||Sorry for the distrubance guys. I figured it out myself.
The problem is that I did not clear the cache from the C# application.
If I did, the same results show.
Jeeji
jeeji skrev:
> Hi.
> I thought I made it clear that it was just a fragment of the code. But
> now something new came to my attention.
> I can see that if I clear the SQL server cache and call the first SQL
> statement (the one without the temporary table), the SQL profiler shows
> that this call takes longer (around twice) than it takes if I clear the
> server cache and run the temporary table version of the call.
> However if I do the same calls from a C# application, the opposite
> occurs.
> To clear the SQL server cache I use:
> DBCC FreeSystemCache('All')
> And for the C# application I am using .Net 2.0, using the
> System.Data.SQLClient calls.
> Any advice would just be great.
> Arnie Rowland wrote:
> > Uri,
> >
> > I think that is just a small fragment of the query. (At least I hope it's
> > just a fragment...)
> >
> > --
> > Arnie Rowland, YACE*
> > "To be successful, your heart must accompany your knowledge."
> >
> > *Yet Another Certification Exam
> >
> >
> > "jeeji" <jihad_dk@.yahoo.com> wrote in message
> > news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> > > Hi
> > >
> > > I have a query which I would like to optimize and thought I had found a
> > > solution.
> > >
> > > The query is a join of several selects from several different tables.
> > >
> > > So we have in general the following:
> > >
> > > SELECT Col1 FROM
> > > (
> > > SELECT table1.Col1 FROM table1 WHERE exp1
> > > JOIN
> > > SELECT table1.Col1 FROM table2 WHERE exp2
> > > ON table1.Col1 = table2.Col1
> > > )
> > >
> > > or something like that. Of course my query was a lot larger than this,
> > > but it is just to illustrate the problem.
> > >
> > > The above was too slow, and I made an attempt to optimize it and
> > > thought it did work. The following is what I did:
> > > Instead of the SELECT's I could create temporary tables and select into
> > > those so I get the following:
> > >
> > > SELECT table1.Col1
> > > INTO #temp_table1
> > > FROM table1 WHERE exp1
> > >
> > > SELECT table1.Col1
> > > INTO #temp_table2
> > > FROM table2 WHERE exp2
> > >
> > > And then join those two tables:
> > >
> > > SELECT Col1 FROM
> > > (
> > > #temp_table1
> > > JOIN
> > > #temp_table2
> > > ON #temp_table1.Col1 = #temp_table2.Col1
> > > )
> > >
> > > and at the end drop the temporary tables.
> > >
> > > DROP Table #temp_table1
> > > DROP Table #temp_table2
> > >
> > > This gives the same result as the original statement.
> > >
> > > I profiled a bit and saw that if I created the temp tables, and ran the
> > > join statement several times after each other, the first time it is run
> > > it take a lot of time compared to the following. Can anyone explain to
> > > me why this is the case? I thought that the temporary tables were
> > > created and loaded into the cache, and therefore all calls to the big
> > > JOIN statement should take equally long time.
> > > The thing is that I cannot reuse the temporary tables, so I get no
> > > benefit from all the above.
> > >
> > > Thank you very much in advance
> > > Jeeji
> > >sql

Optimize this query?

I have a nightly job that aggregates data into a summary table. Each night, an additional item may or may not be added, so the table is cleared and repopulated. I've got this working fine. Now, I am being asked to track when an item first appears in the summary table. Here is the code I am using to do this. I realize it is very poor performance, but I don't know how to make it better.

MyTable Columns:
id INT PK
,v1 VARCHAR(4)
,v2 VARCHAR(10
,v3 DECIMAL
,item1 BIT
,item2 BIT
,item3 BIT

MyTable2 Columns:
v1 VARCHAR(4)
,v2 VARCHAR(10
,v3 DECIMAL
,item INT
,FirstKnownDate DATETIME

All columns are NOT NULL.

1DECLARE @.iNextRowIDINT2 ,@.iCurrentRowIDINT3 ,@.iLoopControlINT4 ,@.v1VARCHAR(4)5 ,@.v2VARCHAR(10)6 ,@.v3DECIMAL78/* This is a loop that executes for a dynamic number of items, eg item1=1, then item2=1, etc */9SET @.iLoopControl=110SELECT @.iNextRowID=MIN(id)FROM MyTableWHERE item1=11112IFISNULL(@.iNextRowID,0) <> 0BEGIN13 SELECT @.iCurrentRowID = id14 ,@.v1 = v115 ,@.v2 = v216 ,@.v3 = v317FROM MyTable18WHERE id=@.iNextRowID1920WHILE @.iLoopControl = 1BEGIN21 IFNOT EXISTS(SELECT *FROM MyTable2WHERE v1=@.v2AND v2=@.v2AND v3=@.v3AND item=1)BEGIN22 INSERT INTO MyTable223/* COLUMN */ (v1,v2,v3,item,firstknowndate)24SELECT @.v1, @.v2, @.v3, 1,GETDATE()25END2627 SELECT @.iNextRowID =NULL28SELECT @.iNextRowID =MIN(id)FROM MyTableWHERE id>@.iCurrentRowIDAND item1=12930IFISNULL(@.iNextRowID,0) = 0BEGIN31 BREAK32 END3334 SELECT @.iCurrentRowID = id35 ,@.v1 = v136 ,@.v2 = v237 ,@.v3 = v338FROM MyTable39WHERE id=@.iNextRowID40END41END
42/* This is the end of the items loop */

This query takes 5 minutes to do straight inserts without the test for exists in the loop for 3 items.  MyTable has just under 96,000 rows in it. MyTable2 has 121,857 rows in it. It is not about 55 minutes and it is still running with the exists so that a row isn't inserted twice. Is there anything I can do to optimize this?

 EDIT: The query just finished running again with the test for exists on each row. It shouldn't have inserted any new rows, but it took 57 minutes and inserted another 114,115 rows. Not good. Anyone see what is wrong with the query?

Have a look at the execution plan and see what costs are associated with it.

|||

This should be obvious just by looking at the query. There are so many select statements, at least 125,000 exists statements. Obviously it won't finish in a few seconds. I just mean, is there a better way to loop through each row and insert or do nothing based on whether that row exists in the second table?

|||

I just meant that it would show you exactly where the bottlenecks exist. As for improving the query, you could probably just join to the second table and perform inserts for all the missing rows.

|||

Code sample? Eliminating the exists is really what is needed.

optimize the stored procedure

CREATE PROCEDURE emp_summary

@.emp_id int ,@.start_date datetime=0,@.end_date datetime=0

AS

SET NOCOUNT ON

IF @.start_date=0 AND @.end_date=0

BEGIN

SET @.end_date=getdate()

SELECT *
FROM emp WHERE emp_id_id=@.emp_id AND a.join_date>@.start_date AND a.joindate<=@.end_date

END

ELSE

SELECT *
FROM emp WHERE emp_id_id=@.emp_id AND a.join_date>@.start_date AND a.joindate<=@.end_date+1
GO

This is the Stored procedure i wrote to get the emp summary with date range and with no date ranges.If i pass start_date and end_Date Sp executes 'else' part if dont pass the parameters it execultes 'IF' part.Can i optimize this SP further?I'd use:CREATE PROCEDURE
@.emp_id INT
, @.start_date DATETIME = NULL
, @.end_date DATETIME = NULL
AS

SELECT *
FROM emp
WHERE emp_id = @.emp_id
AND join_date BETWEEN Coalesce(@.start_date, join_date) AND Coalesce(@.end_date, join_date)

RETURN
GOIf you can't afford the table scan, I'd resort to using dynamic SQL, but I'd try this first.

-PatP

Optimize the query performance?

Hi,
I want to improve performance of my query to ensure that my query does not
timeout.
Scenario:
Table : EmpCheckInCheckOut
Felds: RowNumber,TokenID,CheckinTime,CheckoutTi
me,Action,EmpName
Query : Select TokenID,CheckinTime,CheckoutTime,EmpName
,RowNumber from
EmpCheckInCheckOut EC
WHERE (Action = 'In' AND NOT EXISTS ( SELECT TokenID FROM
EmpCheckInCheckOut EC2 WHERE Action = 'Out' and TokenID = EC.TokenID and
CheckinTime = EC.CheckinTime ) ) OR Action ='Out'
Through this query I get all those employees who have checked-in but not
checked out yet and employee who have done checkin and checkout both.
Clustered Index: Action,TokenIDCheckinTime
Query is working fine. But when I bulk insert 400000 records into this
table, query timeout.
So how well I can create Index on this table so that I get query performance
as well as less bulk insert time.
Please give your suggestion to optimize this query or do I need to change
the existing index and create some other index or something else?
Thanks in Advance
PushkarPushkar
It is probable page spliting casued by insertion. Either remove all indexes
just before BULK INSERT and re-create after or re-build them after the
insertion.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OOnxDdFOGHA.4052@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I want to improve performance of my query to ensure that my query does not
> timeout.
> Scenario:
> Table : EmpCheckInCheckOut
> Felds: RowNumber,TokenID,CheckinTime,CheckoutTi
me,Action,EmpName
> Query : Select TokenID,CheckinTime,CheckoutTime,EmpName
,RowNumber from
> EmpCheckInCheckOut EC
> WHERE (Action = 'In' AND NOT EXISTS ( SELECT TokenID FROM
> EmpCheckInCheckOut EC2 WHERE Action = 'Out' and TokenID = EC.TokenID and
> CheckinTime = EC.CheckinTime ) ) OR Action ='Out'
>
> Through this query I get all those employees who have checked-in but not
> checked out yet and employee who have done checkin and checkout both.
> Clustered Index: Action,TokenIDCheckinTime
> Query is working fine. But when I bulk insert 400000 records into this
> table, query timeout.
> So how well I can create Index on this table so that I get query
> performance as well as less bulk insert time.
> Please give your suggestion to optimize this query or do I need to change
> the existing index and create some other index or something else?
> Thanks in Advance
> Pushkar
>
>|||Action is a poor choice for the first column of the clustered index.
The ideal is that the first column of any index, but most especially
the clustered, be highly selective. Action appears to be the least
selective choice available.
Looking at this query I woud suggest clustering on either of these:
TokenID,CheckinTime,Action
TokenID,Action,CheckinTime
If you do not change the clustering, then you need a second,
non-clustered index on one or the other of those.
For the bulk insert problem, if your regular processing removes all
rows from the table and then loads them again the idea of dropping the
indexes before the load and putting them back after is a good one. An
alternative would be to have another table,
EmpCheckInCheckOut_Imported, with the same layout but NO indexes. Bulk
insert into that table, then use INSERT/SELECT into the production
table. I would use an ORDER BY on the SELECT that matcches the
clustered index key - might help, can't hurt.
Roy
On Thu, 23 Feb 2006 14:28:04 +0530, "Pushkar"
<pushkartiwari@.gmail.com> wrote:

>Hi,
>I want to improve performance of my query to ensure that my query does not
>timeout.
>Scenario:
>Table : EmpCheckInCheckOut
>Felds: RowNumber,TokenID,CheckinTime,CheckoutTi
me,Action,EmpName
>Query : Select TokenID,CheckinTime,CheckoutTime,EmpName
,RowNumber from
>EmpCheckInCheckOut EC
> WHERE (Action = 'In' AND NOT EXISTS ( SELECT TokenID FROM
>EmpCheckInCheckOut EC2 WHERE Action = 'Out' and TokenID = EC.TokenID and
>CheckinTime = EC.CheckinTime ) ) OR Action ='Out'
>
>Through this query I get all those employees who have checked-in but not
>checked out yet and employee who have done checkin and checkout both.
>Clustered Index: Action,TokenIDCheckinTime
>Query is working fine. But when I bulk insert 400000 records into this
>table, query timeout.
>So how well I can create Index on this table so that I get query performanc
e
>as well as less bulk insert time.
>Please give your suggestion to optimize this query or do I need to change
>the existing index and create some other index or something else?
>Thanks in Advance
>Pushkar
>|||Does action track anythign other than check in and check out? If not, then
you could remove action entirely and just use the checkinTime and
CheckoutTime. You would know whether the employee checked out or not by
checking to see if Checkouttime was null. This assumes that, once checked
in, an employee cannot check in again until they have first checked out.
Having seperate rows for check in and check out, and seperate columns for
the same, seems redundant and can probably be simplified by removing on or
the other.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OOnxDdFOGHA.4052@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I want to improve performance of my query to ensure that my query does not
> timeout.
> Scenario:
> Table : EmpCheckInCheckOut
> Felds: RowNumber,TokenID,CheckinTime,CheckoutTi
me,Action,EmpName
> Query : Select TokenID,CheckinTime,CheckoutTime,EmpName
,RowNumber from
> EmpCheckInCheckOut EC
> WHERE (Action = 'In' AND NOT EXISTS ( SELECT TokenID FROM
> EmpCheckInCheckOut EC2 WHERE Action = 'Out' and TokenID = EC.TokenID and
> CheckinTime = EC.CheckinTime ) ) OR Action ='Out'
>
> Through this query I get all those employees who have checked-in but not
> checked out yet and employee who have done checkin and checkout both.
> Clustered Index: Action,TokenIDCheckinTime
> Query is working fine. But when I bulk insert 400000 records into this
> table, query timeout.
> So how well I can create Index on this table so that I get query
performance
> as well as less bulk insert time.
> Please give your suggestion to optimize this query or do I need to change
> the existing index and create some other index or something else?
> Thanks in Advance
> Pushkar
>
>

Optimize the query performance

Hi,
I am saving the data of profiler into trace table every day and then export
this data into table called RepositoryTable.
RepositoryTable has exactly same structure as profiler trace table.
Every day I am inserting 10 lakhs rows of trace events in the Repository
table.
Repository table has primary key on RowNumber and non clustered index on
StartTime column.
I have specified timeout interval as 60 seconds.
I have a simple query based on time range filter only, but this query too
timeout after 60 seconds.
How can I optimize my query to get the best performance?
Is timeout interval too small? What should be optimum timeout interval
assuming repository table will have 10 crores of rows within some time
period?
What is optimum interval to update the statistics of the table?
Thanks in advance.
PushkarSorry, but what are lakhs and crores?
Also, post DDL for your table and indexes, along with your query. See
instructions here:
http://www.aspfaq.com/etiquette.asp?id=5006
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:u0bb2R2eGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am saving the data of profiler into trace table every day and then
export
> this data into table called RepositoryTable.
> RepositoryTable has exactly same structure as profiler trace table.
> Every day I am inserting 10 lakhs rows of trace events in the Repository
> table.
> Repository table has primary key on RowNumber and non clustered index on
> StartTime column.
> I have specified timeout interval as 60 seconds.
> I have a simple query based on time range filter only, but this query too
> timeout after 60 seconds.
> How can I optimize my query to get the best performance?
> Is timeout interval too small? What should be optimum timeout interval
> assuming repository table will have 10 crores of rows within some time
> period?
> What is optimum interval to update the statistics of the table?
>
> Thanks in advance.
> Pushkar
>
>|||Pushkar,
The nonclustered index on StartTime is probably not useful. Currently,
the query is probably using a table scan (or clustered index scan) which
will take more time as the table grows. Then can check this by viewing
the query plan.
If you make the index on StartTime the clustered index, then only these
rows will be read when copying a date range. After that, maybe the query
will finish within the timeout period of 60 seconds.
HTH,
Gert-Jan
P.S. Jim, you might want to have a look at
http://en.wikipedia.org/wiki/Lakh This page also explains crores.
Pushkar wrote:
> Hi,
> I am saving the data of profiler into trace table every day and then expor
t
> this data into table called RepositoryTable.
> RepositoryTable has exactly same structure as profiler trace table.
> Every day I am inserting 10 lakhs rows of trace events in the Repository
> table.
> Repository table has primary key on RowNumber and non clustered index on
> StartTime column.
> I have specified timeout interval as 60 seconds.
> I have a simple query based on time range filter only, but this query too
> timeout after 60 seconds.
> How can I optimize my query to get the best performance?
> Is timeout interval too small? What should be optimum timeout interval
> assuming repository table will have 10 crores of rows within some time
> period?
> What is optimum interval to update the statistics of the table?
> Thanks in advance.
> Pushkar|||Gert-Jan,
Thanks for the link.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:446E08D7.83FF2ACE@.toomuchspamalready.nl...
> Pushkar,
> The nonclustered index on StartTime is probably not useful. Currently,
> the query is probably using a table scan (or clustered index scan) which
> will take more time as the table grows. Then can check this by viewing
> the query plan.
> If you make the index on StartTime the clustered index, then only these
> rows will be read when copying a date range. After that, maybe the query
> will finish within the timeout period of 60 seconds.
> HTH,
> Gert-Jan
> P.S. Jim, you might want to have a look at
> http://en.wikipedia.org/wiki/Lakh This page also explains crores.
>
> Pushkar wrote:
export
toosql

Optimize the Query

Listed below is tsql statement that I set up execute every 60 minutes.
To check to see if all the values exist in the following list: '806478',
'806479','806480','806481' in the Call_Movements table.
I am only interest in the first six characters and I'm converting iNum data
type from money to varchar(20).
left(cast(iNum as varchar(20)),6)
Please help me optimize the t-sql statement listed below since this table is
large.
Is there away to change the following to binary format:
DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
listed below to be optimal?
Thank You,
T-SQL
DECLARE @.CNT_MTN_REC_0 SMALLINT
SET @.CNT_MTN_REC_0 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806478'))
--
-- Count the
--
DECLARE @.CNT_MTN_REC_1 SMALLINT
SET @.CNT_MTN_REC_1 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806479'))
--
--
--
DECLARE @.CNT_MTN_REC_2 SMALLINT
SET @.CNT_MTN_REC_2 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806480'))
--
--
--
DECLARE @.CNT_MTN_REC_3 SMALLINT
SET @.CNT_MTN_REC_3 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806481'))
--
--
--
if (@.CNT_MTN_REC_0 = 0) OR (@.CNT_MTN_REC_1 = 0) OR (@.CNT_MTN_REC_2 = 0) OR
(@.CNT_MTN_REC_3 = 0)
BEGIN
PRINT "Error has Occurred'
ENDJoe K. (Joe K.@.discussions.microsoft.com) writes:
> To check to see if all the values exist in the following list: '806478',
> '806479','806480','806481' in the Call_Movements table.
> I am only interest in the first six characters and I'm converting iNum
> data type from money to varchar(20).
> left(cast(iNum as varchar(20)),6)
> Please help me optimize the t-sql statement listed below since this
> table is large.
> Is there away to change the following to binary format:
> DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
> listed below to be optimal?
It would have helped if you had posted the CREATE TABLE and CREATE INDEX
statements for the tables.
But first, there is no need to run four SQL statemennts.
This could either be done as:
Select count(iNum), left(cast(iNum as varchar(20)),6)
from Call_Movements
where StartTime >= DATEADD(mi, -60, GETDATE())
AND left(cast(iNum as varchar(20)),6) IN
('806478', '806479', '806480', '806481')
GROUP BY left(cast(iNum as varchar(20)),6)
This produces a result set of four rows. If you need to get the result
into variables, you can do:
Select @.CNT_MTN_REC_0 = SUM(CASE left(cast(iNum as varchar(20)),6)
WHEN '806478' THEN 1
ELSE 0
END),
..
from Call_Movements
where StartTime >= DATEADD(mi, -60, GETDATE())
AND left(cast(iNum as varchar(20)),6) IN
('806478', '806479', '806480', '806481')
As you can see, I have also changed the condition on Start_Time, in case
this column is indexed. When an indexed column appears in an expression
like in your query, the index is of on use. The query is still problematic
due to the >=. If the index on StartTime is clustered it is not much of
an issue, but if there is only a onn-clustered index, the optimizer is not
likely to pick it in this case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Joe,
Will you have iNum values that start with 806478 (or any of the other 3
values) followed by other numbers before the decimal point? For example
8064781.01?
If not, then make sure you add a nonclustered index on
Call_Movement(iNum, StartTime) and add the following predicates to the
WHERE clause:
AND iNum >= 806478
AND iNum < 806482
You could try the following query:
IF ( SELECT COUNT(DISTINCT CAST(iNum as char(6)) )
FROM Call_Movements
WHERE StartTime >= DATEADD(minute, -60, CURRENT_TIMESTAMP)
AND iNum >= CAST(806478 AS money)
AND iNum < CAST(806482 AS money)
AND CAST(iNum as char(6)) IN ('806478', '806479', '806480',
'806481')
) < 4
BEGIN
PRINT "Error has Occurred'
END
HTH,
Gert-Jan
Joe K. wrote:
> Listed below is tsql statement that I set up execute every 60 minutes.
> To check to see if all the values exist in the following list: '806478',
> '806479','806480','806481' in the Call_Movements table.
> I am only interest in the first six characters and I'm converting iNum dat
a
> type from money to varchar(20).
> left(cast(iNum as varchar(20)),6)
> Please help me optimize the t-sql statement listed below since this table
is
> large.
> Is there away to change the following to binary format:
> DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
> listed below to be optimal?
> Thank You,
>
> T-SQL
> DECLARE @.CNT_MTN_REC_0 SMALLINT
> SET @.CNT_MTN_REC_0 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806478'))
> --
> -- Count the
> --
> DECLARE @.CNT_MTN_REC_1 SMALLINT
> SET @.CNT_MTN_REC_1 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806479'))
> --
> --
> --
> DECLARE @.CNT_MTN_REC_2 SMALLINT
> SET @.CNT_MTN_REC_2 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806480'))
> --
> --
> --
> DECLARE @.CNT_MTN_REC_3 SMALLINT
> SET @.CNT_MTN_REC_3 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806481'))
> --
> --
> --
> if (@.CNT_MTN_REC_0 = 0) OR (@.CNT_MTN_REC_1 = 0) OR (@.CNT_MTN_REC_2 = 0) OR
> (@.CNT_MTN_REC_3 = 0)
> BEGIN
> PRINT "Error has Occurred'
> END|||And encapsulate LEFT operation to sub-query may be get more good
performance.
Gert-Jan Strik =E5=86=99=E9=81=93=EF=BC=9A
> Joe,
> Will you have iNum values that start with 806478 (or any of the other 3
> values) followed by other numbers before the decimal point? For example
> 8064781.01?
> If not, then make sure you add a nonclustered index on
> Call_Movement(iNum, StartTime) and add the following predicates to the
> WHERE clause:
> AND iNum >=3D 806478
> AND iNum < 806482
> You could try the following query:
> IF ( SELECT COUNT(DISTINCT CAST(iNum as char(6)) )
> FROM Call_Movements
> WHERE StartTime >=3D DATEADD(minute, -60, CURRENT_TIMESTAMP)
> AND iNum >=3D CAST(806478 AS money)
> AND iNum < CAST(806482 AS money)
> AND CAST(iNum as char(6)) IN ('806478', '806479', '806480',
> '806481')
> ) < 4
> BEGIN
> PRINT "Error has Occurred'
> END
>
> HTH,
> Gert-Jan
>
> Joe K. wrote:
data
le is
=3D 0) OR|||"navyzhu@.gmail.com" wrote:
> And encapsulate LEFT operation to sub-query may be get more good
> performance.
I have not done performance tests to disprove it, but I highly doubt it!
I don't think LEFT will outperform CAST.
Besides, why use the proprietary LEFT when the standard CAST will do
just fine...
Gert-Jan
> Gert-Jan Strik 写道：
>

Optimize Temp tables

Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
With that much code running just to generate a report, you might consider
having a background process (or a data warehouse) pre-aggregate the data.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||In addition to Aaron's response , create a temp tables at the beginning of
the stored procedure (reduce recompile).
Also, it may help to improve performance if you add NC/CI indexes depending
on your requrements.
Look at an execution plan of the stored procedure to ensure that optimizer
is available to use indexes.
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegr oups.com...
> Hi All,
> My application makes heavy use of temp tables because we have a few
> thousands of stored proc running every day to renerate reports.
> What are the positive and negative impact to add clustered index on the
> temp table?
> what is the negative impact if I use derived table instead temp table?
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
> Thanks so much,
> Silaphet,
>
|||Another possibility would be to consider an INDEXED VIEW.
Typically, you want to keep your OLTP and DSS/OLAP operations and data
seperated. It might be wise to reconsider the cohosting of this data in the
same database and reconsider creating something new elsewhere that better
fits your architecture.
Sincerely,
Anthony Thomas

"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegr oups.com...
Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,

Optimize Temp tables

Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
With that much code running just to generate a report, you might consider
having a background process (or a data warehouse) pre-aggregate the data.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||In addition to Aaron's response , create a temp tables at the beginning of
the stored procedure (reduce recompile).
Also, it may help to improve performance if you add NC/CI indexes depending
on your requrements.
Look at an execution plan of the stored procedure to ensure that optimizer
is available to use indexes.
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
> Hi All,
> My application makes heavy use of temp tables because we have a few
> thousands of stored proc running every day to renerate reports.
> What are the positive and negative impact to add clustered index on the
> temp table?
> what is the negative impact if I use derived table instead temp table?
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
> Thanks so much,
> Silaphet,
>|||Another possibility would be to consider an INDEXED VIEW.
Typically, you want to keep your OLTP and DSS/OLAP operations and data
seperated. It might be wise to reconsider the cohosting of this data in the
same database and reconsider creating something new elsewhere that better
fits your architecture.
Sincerely,
Anthony Thomas
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,

Optimize Temp tables

Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
With that much code running just to generate a report, you might consider
having a background process (or a data warehouse) pre-aggregate the data.
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||In addition to Aaron's response , create a temp tables at the beginning of
the stored procedure (reduce recompile).
Also, it may help to improve performance if you add NC/CI indexes depending
on your requrements.
Look at an execution plan of the stored procedure to ensure that optimizer
is available to use indexes.
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
> Hi All,
> My application makes heavy use of temp tables because we have a few
> thousands of stored proc running every day to renerate reports.
> What are the positive and negative impact to add clustered index on the
> temp table?
> what is the negative impact if I use derived table instead temp table?
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
> Thanks so much,
> Silaphet,
>|||Another possibility would be to consider an INDEXED VIEW.
Typically, you want to keep your OLTP and DSS/OLAP operations and data
seperated. It might be wise to reconsider the cohosting of this data in the
same database and reconsider creating something new elsewhere that better
fits your architecture.
Sincerely,
Anthony Thomas
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,

Optimize sql statements / find usefull indices

Dear all,

I try to find an easy method (like 'explain' in MySQL) to optimize my SQL statements and create usefull indices. I have created a trace table with the PROFILER and filtered the SQL statements by long DURATION time.

In the next step I used:

set SHOWPLAN_ALL ON;
my_sql_statment

to find the correct indices. Is there any tutorial available describes how to analyse such an output to find correct indices?

Best regards
febel

If you have SQL Profiler trace you could use Database Engine Tuning Advisor. Its wizard, that analyze trace and propose indexes, statictics etc

As tutorial you could use this book http://www.microsoft.com/MSPress/books/8565.aspx

|||Dear Konstantin,

thanks for your answer, but I have only the trace table and no possibility to use Database Engine Tuning Advisor. In addition I want use this in a programm which should set the indices in a automatic way depending on the results of the analysis of the trace table.

Best regards
febel
sql

Optimize SQL

Hello All!
Is there a way to write this SQL without a UNION, because where i have to
set one field to null to match the number of field of the second sql :(
SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
AgenciaID, 'Caixa' AS Tipo
FROM Caixa
UNION
SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
Agencia.ID AS AgenciaID, 'Banco' AS Tipo
FROM Agencia JOIN
BANCO ON Banco.ID = Agencia.ID
Thank you all!!!
Bruno NThere probably are other solutions but if you want the same columns in
there then you will presumably have to populate the AgenciaID column
with NULLs anyway. I'm not sure what it is you want to do differently.
You should consider using UNION ALL instead of UNION unless it's
required to eliminate duplicates. UNION ALL will typically perform
better.
If you need more help, please post some more info as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Nope, unless you want to return 2 separate result sets. Then you can do
without the Tipo column as well.
There's nothing wrong really with setting one column to NULL if it doesn't
exist in that part of the unioned set.
Jacco Schalkwijk
SQL Server MVP
"Bruno N" <nylren@.hotmail.com> wrote in message
news:eHHIkgSNFHA.3788@.tk2msftngp13.phx.gbl...
> Hello All!
> Is there a way to write this SQL without a UNION, because where i have to
> set one field to null to match the number of field of the second sql :(
> SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
> AgenciaID, 'Caixa' AS Tipo
> FROM Caixa
> UNION
> SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
> Agencia.ID AS AgenciaID, 'Banco' AS Tipo
> FROM Agencia JOIN
> BANCO ON Banco.ID = Agencia.ID
> Thank you all!!!
> Bruno N
>|||Thanks guys!!
"Bruno N" <nylren@.hotmail.com> escreveu na mensagem
news:eHHIkgSNFHA.3788@.tk2msftngp13.phx.gbl...
> Hello All!
> Is there a way to write this SQL without a UNION, because where i have to
> set one field to null to match the number of field of the second sql :(
> SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
> AgenciaID, 'Caixa' AS Tipo
> FROM Caixa
> UNION
> SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
> Agencia.ID AS AgenciaID, 'Banco' AS Tipo
> FROM Agencia JOIN
> BANCO ON Banco.ID = Agencia.ID
> Thank you all!!!
> Bruno N
>

Optimize speed of Hosted SQL Server

Hi,

I'm retrieving data in VBA using simple SELECT statements on my Hosted SQL Server. How do I most effectively speed up the process?

Is it just about data amount, traffic, and speed of Internet conncetion?

Or should I use Stored Procedures or should I index tables?

Is there a function to kind of "flush" the data to the VBA program as it is all READ-ONLY?

Any help is much appreciated,

Jakob

Can you give more information about how your database schema looks like, what type of queries you are doing, and how your network architecture looks like?

Based on that it might be easier to answer your questions.

Thanks,

Marcel van der Holst
[MSFT]|||

The queries are very simple, just simple "SELECT xx, xx, xx FROM xx WHERE xx=xx"

I'm connecting from home with my lapto to a hosted SQL Server, so no big network. I have tried testing my code to see how and when it is slow. It it quite fast to open the connection, but when I try to through the records using for example the rs.movenext function I can see that it takes a second or two each time for each record and each field in each record. It seems like I'm maintaining an open connection over the internet when I actually just want to the data read-only.

Does that help?

Jakob

|||Would it be possible to run the queries against a local SQL Server that runs on your laptop. That way, you can ignore the network latency, and see if the query is causing the problem.

If you use RecordSet, it will keep the connection open until you explicitely close it. Even when you close it, connection pooling will be used to keep the connection open a while, just in case you need it again.

How much data do you select, i.e. how big is the data in the select xx,xx statement? if that data is big, and you have to go over a big network, it might cause the slowdown..

Another thing to consider is to retrieve all the data you need in a big select statement (select * from Table), and then store this data in a local cache, and do the searching and filtering locally, instead of doing it over the internet.

Thanks,

Marcel van der Holst
[MSFT]|||

the database is about 30mb. The queries are quite simple - just "SELECT xx, xx FROM xx WHERE xx=yy".

I just converted my Access 2003 database, which was stored locally, to hosted SQL Server. An update which previously took about 10 seconds now takes about 2 minutes.

I tried to limit the number of rows which helped quite a lot. Then I also tried to change the format of a field from "nText" to "nVar" that also helped a lot. It seems like the amount of data transmitted is the single most important factor - even though I'm running on a 8mbit line.

Most of my data I just need in arrays - I usually don't update any tables. My thought was that it should be possible to send the query result from the SQL Server in some kind of "flat" text array format and not in a recordset format.

I'm using ADODB in VBA with code lines like:

Set rsData = New ADODB.Recordset

rsData.Open sSql, conGPAM, adOpenKeyset, adLockOptimistic

aData = rsData.GetRows(.1)

I have tried to change LockType and CursorType, but that doesn't really make a difference.

Any suggestions?
|||

ADO is horrible for direct access. Use sprocs. Use output parameters if guaranteed to return just one row or if you only need a single output (like a count).

Check the network latency to your host.

Appropriate indexing is paramount for optimal perfomance. This is somewhat science and somewhat art/experience.