Sql Edition Selection: efficient

Showing posts with label efficient. Show all posts

Friday, March 9, 2012

email address domain strip of query

Could this query be made more efficient?
It takes ages to run it. It strips of everything from an email address and keeps only the domain.

SUBSTRING(Mailaddress, CHARINDEX('@.', Mailaddress) + 1, CHARINDEX('.', SUBSTRING(Mailaddress, CHARINDEX('@.', Mailaddress) + 1, 100)) - 1) AS mail_domaingot 2005? string manipulation is faster in CLR.|||unfortunately no i don't. I may upgrade soon but for now it is 2000 im using.|||I assume that if you take this column out of the select list, the plan remains the same, but the query time goes up? Does the query time remain the same when you add the raw email address to the select list (and comment out all the substringing)? I am wondering, if you accidentally got a covering index on all of the fields not associated with the email addresses, or maybe the query is using different indexes for the query with all the substrings.

Also, not to be a nit picker, but which part of the domain are you after? An email address can be very complex. My brother has an email address that ends in @.haystack.mit.edu. Some of our British posters would have addresses like @.company.co.uk. I am guessing you would be interested in the "mit" or the "company", but not in "co' or "haystack".|||yes its from select list and no joins so it should be very fast. I se now that i have to make this to work with thoose split domain names aswell... damn :)

My question about the query was more kinda of " is this a stupid way to do this"?
i can live with the time it takes to run this query it is after al quite a large table.

thx for your help

Friday, February 24, 2012

Efficient INSERT of rows- .NET

Hello- I have a C++ .NET application that rips through a raw file, generating
thousands of INSERT statements to insert into a SQL Server 2000 database
through the SQLConnection class in .NET. While this seems reasonably fast,
I'm wondering if it is the most efficient way of doing this. Would creating
a stored procedure on the SQL Server and then passing parameters to the SP
through the Command object be faster and more efficient? I've also
considered dumping the contents to a flat CSV file and using SQL Server DTS
to BULK INSERT the rows- but that is additional overhead in creating the CSV
file and then launching DTS etc. Any suggestions/comments would be
appreciated.
Thanks,
Jon
I replied to this in .programming. Please don't multipost. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Porter" <JonathanPorter@.discussions.microsoft.com> wrote in message
news:D72A8997-D492-49A4-86DB-16C786771F3F@.microsoft.com...
> Hello- I have a C++ .NET application that rips through a raw file, generating
> thousands of INSERT statements to insert into a SQL Server 2000 database
> through the SQLConnection class in .NET. While this seems reasonably fast,
> I'm wondering if it is the most efficient way of doing this. Would creating
> a stored procedure on the SQL Server and then passing parameters to the SP
> through the Command object be faster and more efficient? I've also
> considered dumping the contents to a flat CSV file and using SQL Server DTS
> to BULK INSERT the rows- but that is additional overhead in creating the CSV
> file and then launching DTS etc. Any suggestions/comments would be
> appreciated.
> Thanks,
> Jon

Sunday, February 19, 2012

Efficient way to transfer huge amount of records

Hi All,

I used a data flow task, and when trying to transfer data from a OLE DB Source (records ~ 75 lac) to a destination OLE DB Source, SSIS fails at the middle giving an error saying the Transaction log got filled, try again after clearing the same.

My query is what is the most efficient way to transfer say records more than 50 lac ensuring that it doesn't fail in the middle?

Thanks in Advance,

Mithun.

To avoid the error you need to increase transaction log size in SQL Server - it is SQL's transaction log, not SSIS'. I believe you can also decrease the batch size in OLE DB destination properties.

The most efficient way is SQL Destination - if the destination server is on the same machine where package runs, it is faster than OLE DB Destination.|||

Thanks for the comments!! I'll try out the options.

|||

Michael,

But if you need to copy from .xls source into a table you can't use Sql Destination.

Correct me if I'm going wrong.

|||

enric vives wrote:

Michael,
But if you need to copy from .xls source into a table you can't use Sql Destination.
Correct me if I'm going wrong.

That's wrong. The source of the data is irrelevant. There are some pre-requisites to using SQL Server Destination and the April 2006 drop of BOL tells you all you need to know.

-Jamie

Efficient way to store game data: help?

Hello everyone!

So I am trying to wrap my head around the most efficient way to store some game data in sql server 2005.

I wont list all of my tables or columns as there would just be too many, but will try to post as exact as I can.

Basically lets say I have 3000 monsters in my game, and over 8000 items some of which are monster loot.

Each monster could have 1 drop item or they could have 200 different item drops all depending on the monster.

So I am just trying to find the best method of creating a loot table.

So I have the following in mind.

[MonsterTable]

monsterID;

monsterName;

etc...

[ItemTable]

itemID;

itemName;

etc..

[LootTable]

lootTableID;

refMonsterID;

refItemID;

So I'm not worried about how I would get all of the data in there, I'm just wondering if this simple method of using Ref. ID's between tables would provide the best method for extracting the data when needed.

Any ideas or thoughts are more then welcome, any questions just ask, and thanks for any guidance or information you can provide on a more efficient manner.

From the situation / information provided, this is an appropiate and common design.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

efficient way to put multiple tables in one xml output

hi guys,

i'm looking for alternatives on how to put multiple tables in one xml output.

i have this .net program that has been passed to me that currently loops through some tables and calls an sp that returns a query that has a FOR XML AUTO, ELEMENTS on it, then appends <Extract> ... </Extract> as a root node for all the extracted tables then saves it as an xml file. i'm trying to optimize it by limiting the number reads to the db server, so one option is for me do the loop inside the sp.

is this efficient? any snippet? by the way, i'm using sql2k

thanks guys.

/rh4m1ll3

hi,

Bit confused with the requirement. plz let me know what you are doing exactly from the below 2 approaches:

go to one table 1 - execute sp - return xml file1 - add <Extract> ... </Extract> as root node

go to one table 2 - execute sp - return xml file2 - add <Extract> ... </Extract> as root node

go to one table 3 - execute sp - return xml file3 - add <Extract> ... </Extract> as root node

..........

( or )

go to one table1 - get some details

go to one table2 - get some details

go to one table3 - get some details

...........

pass details to sp - return xml file - add <extract> .. <extract> as root node

confirm what you are doing?

Regards,

kwaerol.

|||alright this is the current setup

here's the psuedo of the current c# app

Code Snippet

for each table.. {
x += executescalar("theSPwithForXML") //appends each result
}
x = "<Extract>" + x + "</Extract>"
// then saves x as xml file

what i'm trying to achieve is

x = executescalar("theSPthatAppendsAllTablesIntoOneXML")
// then saves x as xml file

since you cannot assign the result of a query with a FOR XML to a variable in SQL2K, i'm looking for other alternatives or workarounds in TSQL, so that "theSPthatAppendsAllTablesIntoOneXML" can return a single xml with all the tables that i need to convert.

the last resort that i'm looking at is returning multiple result instead

CREATE PROCEDURE theSPthatAppendsAllTablesIntoOneXML
AS

SELECT *
FROM Table1
FOR XML AUTO, ELEMENTS

SELECT *
FROM Table2
FOR XML AUTO, ELEMENTS

SELECT *
FROM Table3
FOR XML AUTO, ELEMENTS

-- etc etc

GO|||

Hi,

You are correct. As per my knowledge we cannot take the xml output into a local variable in sql2k.

I feel that every time u do some sql operation and get some xml output

At last want to append <extract> nodes and save that as xml file.

If I am correct. Why don't you get the resultsets at one slot from db and then make use of Load xml using XMldatadocument.

If required you can create one schema file and make use of it to load the xml data document from dataset (having multiple Tables)

Regards,

Subbu

Efficient way than IN statement

I have two tables JDECurrencyRates and JDE Currency Conversion

I want to insert all the records all the records from JDECurrencyRates to JDECurrencyConversion that does not exists in JDECurrencyConversion table. For matching I am to use three keys i.e. FromCurrency, TO Currency and Effdate

To achieve this task i wrote the following query

INSERT INTO PresentationEurope.dbo.JDECurrencyConversion(Date,FromCurrency,FromCurrencyDesc,

ToCurrency, ToCurrencyDesc, EffDate, FromExchRate, ToExchRate,CreationDatetime

,ChangeDatetime)

(SELECT effdate as date, FromCurrency, FromCurrencyDesc, ToCurrency, ToCurrencyDesc, EffDate,

FromExchRate, ToExchRate, GETDATE(),GETDATE() FROM MAINTENANCE.DBO.JDECURRENCYRATES

WHERE FROMCURRENCY NOT IN (SELECT FromCurrency FROM PRESENTATIONEUROPE.DBO.JDECURRENCYCONVERSION)

OR TOCURRENCY NOT IN (SELECT TOCURRENCY FROM PRESENTATIONEUROPE.DBO.JDECURRENCYCONVERSION)

OR EFFDATE NOT IN (SELECT EFFDATE FROM PRESENTATIONEUROPE.DBO.JDECURRENCYCONVERSION))

Can any one suggest me the better way to accomplish this task or this query is OK (or efficient enough)

I think a more efficient way would be to use LEFT OUTER JOIN on your 3 keys and checking for missing values

INSERT INTO ....
SELECT .....
FROM JDECurrencyRates cr
LEFT OUTER JOIN JDECurrencyConversion cc
ON cr.FromCurrency = cc.FromCurrency
AND cr.TOCURRENCY = cc.TOCURRENCY
AND cr.EFFDATE = cc.EFFDATE
WHERE cc.FromCurrency IS NULL

Any field should do in the WHERE clause

NB.
|||

thanx very much for providing efficient way

yes! i have tried and this works fine

|||

Using LEFT JOIN with IS NULL is not the efficient way. NOT EXISTS is the fastest way to perform these type of checks (absence or existence of rows). SQL Server 2000 & 2005 will typically generate the same plan for NOT IN / NOT EXISTS and IN/EXISTS queries. But using EXISTS/NOT EXISTS is always safer because you may not get into situation where you will get wrong results (in case of NOT IN/IN) due to NULL values. The use of LEFT JOIN with NULL check uses more operators than a NOT EXISTS query.

Compare the estimated query plan costs of the queries below:

-- Get list of authors with no titles:

select *
from pubs.dbo.authors as a
where not exists(select * from pubs.dbo.titleauthor as ta
where ta.au_id = a.au_id)

select *
from pubs.dbo.authors as a
left join pubs.dbo.titleauthor as ta
on ta.au_id = a.au_id
where ta.au_id is null

-- Get list of tables with no indexes:

select t.object_id
from sys.tables as t
where not exists(select *
from sys.indexes as i
where i.object_id = t.object_id)

select t.object_id
from sys.tables as t
left join sys.indexes as i
on i.object_id = t.object_id
where i.object_id is null

And depending on your indexes, data and columns referenced in your queries the performance could be even worse. At best, the LEFT JOIN with IS NULL check approach will be as close to the NOT EXISTS query. So you should write your INSERT...SELECT like:

INSERT INTO PresentationEurope.dbo.JDECurrencyConversion

(Date,FromCurrency,FromCurrencyDesc,

ToCurrency, ToCurrencyDesc, EffDate, FromExchRate, ToExchRate,

CreationDatetime ,ChangeDatetime)

SELECT effdate as date, FromCurrency, FromCurrencyDesc

, ToCurrency, ToCurrencyDesc, EffDate, FromExchRate

, ToExchRate, GETDATE(),GETDATE()

FROM MAINTENANCE.DBO.JDECURRENCYRATES as j

WHERE NOT EXISTS(

SELECT *

FROM PRESENTATIONEUROPE.DBO.JDECURRENCYCONVERSION as j1

WHERE j1.FromCurrency = j.FROMCURRENCY

AND j1.TOCURRENCY = j.TOCURRENCY

AND j1.EFFDATE = j.EFFDATE

))

|||

(excuse bad english)

I think that the problem resides in the absent of association between "from..." and "toex" tables. Maybe the day (date) would be the same. Did the number os records inserted exceeded ?

Marcelo

Efficient way of purging old data?

I have a table with no primary key.

The are 6 columns. The first four columns represent one object. There is then a status and a datetime (Updated).

A new record is added into the table whenever a status changes (I need to keep the old status as well).

The problem I have is how to efficiently purge old data.

I need to keep all records with a datetime within the last week.

I also need to ensure that I at least one status is kept for each combination of the first four columns.

The table contains over 150,000 records. About 1,000 statuses change per day.

I want to purge the data once a week - delete about 7,000 records.

This is the query I wrote

delete from table1 where Updated <
(select max(Updated) from table1 t1 where table1.col1 = t1.col1 and table1.col2 = t1.col2
and table1.col3 = t1.col3 and table1.col4 = t1.col4 group by col1, col2, col3, col4)
and Updated < GetDate() - 7

This seems to be fairly fast - records only change a couple of times a week, so the subquery only returns 3 or 4 records. The subquery will be called on almost all the records in the table though.

Can anyone see a more efficient way of doing this?

Well, I'd use a dereived table myself:

delete from table1 t1
inner join
(select col1, col2, col3, col4, max(Updated) as MaxUpdated
from table1
group by col1, col2, col3, col4) t2 on t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col3 = t2.col3 and t1.col4 = t2.col4 and t1.Updated = t2.MaxUpdated
where t1.Updated < GetDate() - 7

That usually seems to perform faster then a subquery, but you'd have to try it for yourself to know for sure.

Efficient table record size

I understand that I can put a massive 8K varchar in one data page, but I want to know if that is the best design.

I have a data stream that can be from 10 to 7000 characters. 90% of the time the field data length is less than 700 characters. Is it better to create one massive varchar field or to create multiple smaller varchar fields? (Perhaps varchar(700) fields ten times.) Or does it matter?

(Also, the record is primarily inserted once then just read from then on.)

The query with the smaller fields would look like this:

SELECT RTRIM(
LEFT( ReportStream1 + SPACE(700), 700) +
LEFT(ISNULL(ReportStream2 , '') + SPACE(700), 700) +
LEFT(ISNULL(ReportStream3 , '') + SPACE(700), 700) +
LEFT(ISNULL(ReportStream4 , '') + SPACE(700), 700) +
LEFT(ISNULL(ReportStream5 , '') + SPACE(700), 700) +
LEFT(ISNULL(ReportStream6 , '') + SPACE(700), 700) +
LEFT(ISNULL(ReportStream7 , '') + SPACE(700), 700) +
LEFT(ISNULL(ReportStream8 , '') + SPACE(700), 700) +
LEFT(ISNULL(ReportStream9 , '') + SPACE(700), 700)
)
FROM ReportStream
WHERE StreamID = 123

It depends on what you want to do with the data. If the data is always selected as one chunk, performance wise, it is better to store it as a single column.

For each varchar column, you get a little bit storage overhead (about 2 bytes per column) that will be stored as well, so having one column is better from storage point of view.

However, if you frequently want to query subsets of your string (i.e. you only are interested in the first few characters, and want to do queries based on that), it makes more sense to have multiple columns, so that you can more easily query and index these substrings.

Thanks,

Marcel van der Holst
[MSFT]

efficient SQL profiling

Hi,
Is there a way to efficiently monitor SQL performance using SQL Profiler,
and my meaning is to see distinct SQL query sent to server (by ignoring
parameter values sent with the queries)
this mean that query like
select * from users where user_id = 10
and
select * from users where user_id = 20
will be shown as the same query counted 2 times, and this way see which
query is performed mostly on the server, and priorities it's performance
tuning.
creative suggestions invited.
thanx.If it is simply the where clause that you are trying to eliminate, then why
not log all your queries to a SQL table and then do a select distinct from
the table, and get the substring of the query that does not include the
where clause?
"z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
news:Od5S7Gh%23DHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there a way to efficiently monitor SQL performance using SQL Profiler,
> and my meaning is to see distinct SQL query sent to server (by ignoring
> parameter values sent with the queries)
> this mean that query like
> select * from users where user_id = 10
> and
> select * from users where user_id = 20
> will be shown as the same query counted 2 times, and this way see which
> query is performed mostly on the server, and priorities it's performance
> tuning.
> creative suggestions invited.
> thanx.
>
>|||Thanx,
2 points:
1. how do i log all my queries to the database in an encapsulated way?
1.1 can i also log this way the time it took to execute?
2. my buttleneck might also be a execute statement - well, this will go also
with your suggestion, just truncated before the starting '('.
"Aaron Relph" <x@.x.com> wrote in message
news:OAEHgVh%23DHA.3436@.tk2msftngp13.phx.gbl...
> If it is simply the where clause that you are trying to eliminate, then
why
> not log all your queries to a SQL table and then do a select distinct from
> the table, and get the substring of the query that does not include the
> where clause?
> "z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
> news:Od5S7Gh%23DHA.3032@.TK2MSFTNGP10.phx.gbl...
Profiler,
>|||This may help -
http://www.sql-server-performance.c...ofiler_tips.asp
Ray Higdon MCSE, MCDBA, CCNA
--
"z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
news:OQuACAi%23DHA.3292@.TK2MSFTNGP11.phx.gbl...
> Thanx,
> 2 points:
> 1. how do i log all my queries to the database in an encapsulated way?
> 1.1 can i also log this way the time it took to execute?
> 2. my buttleneck might also be a execute statement - well, this will go
also
> with your suggestion, just truncated before the starting '('.
>
>
> "Aaron Relph" <x@.x.com> wrote in message
> news:OAEHgVh%23DHA.3436@.tk2msftngp13.phx.gbl...
> why
from
> Profiler,
ignoring
which
performance
>

Efficient SQL Backup?

Hi all,
I am having issues of efficiency of backing up data from one SQL data
base to another.
The two servers in questions are on different networks , behind
different firewalls. We have MS SQL 2000.
On the source data i run a job with the following steps:
1> take trans backup every 4 hrs
2> ftp to the remote server
3> if ftp fails , disable the whole job
On the target server I run a job which does the following
1> restore the trans backup with NORECOVERY.
If the job fails at target. I will have to go through the whole process
of doing a complete backup of the source , restoring it at the other
ens and then starting trans-backup again.
Also, if we do a failover to the target server, then when we roll back
to the source server again we have to da a back-up of the target and
restore it on the source server.
Is ther a more efficent way of doing this'Actually, no. This is pretty much what Log Shipping does. If you failover
to the remote and then want to failback to the primary, then you have to get
all of the data from the remote to the primary which means a backup/restore
cycle.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
<bajaj.vishal@.gmail.com> wrote in message
news:1145456580.413687.286110@.t31g2000cwb.googlegroups.com...
> Hi all,
> I am having issues of efficiency of backing up data from one SQL data
> base to another.
> The two servers in questions are on different networks , behind
> different firewalls. We have MS SQL 2000.
> On the source data i run a job with the following steps:
> 1> take trans backup every 4 hrs
> 2> ftp to the remote server
> 3> if ftp fails , disable the whole job
> On the target server I run a job which does the following
> 1> restore the trans backup with NORECOVERY.
> If the job fails at target. I will have to go through the whole process
> of doing a complete backup of the source , restoring it at the other
> ens and then starting trans-backup again.
> Also, if we do a failover to the target server, then when we roll back
> to the source server again we have to da a back-up of the target and
> restore it on the source server.
> Is ther a more efficent way of doing this'
>

Efficient SQL Backup?

Hi all,
I am having issues of efficiency of backing up data from one SQL data
base to another.
The two servers in questions are on different networks , behind
different firewalls. We have MS SQL 2000.
On the source data i run a job with the following steps:
1> take trans backup every 4 hrs
2> ftp to the remote server
3> if ftp fails , disable the whole job
On the target server I run a job which does the following
1> restore the trans backup with NORECOVERY.
If the job fails at target. I will have to go through the whole process
of doing a complete backup of the source , restoring it at the other
ens and then starting trans-backup again.
Also, if we do a failover to the target server, then when we roll back
to the source server again we have to da a back-up of the target and
restore it on the source server.
Is ther a more efficent way of doing this'Sounds like a typical log shipping scenario. If the job fails at source you
do may have the option of applying the logs manually to bring the standby
server up-to-date without restoring the complete backup.
The link below show the process using Microsoft's log shipping solution but
a manual method will achive the same result.
http://www.microsoft.com/technet/pr...fr=
true
"Vishal" wrote:

> Hi all,
> I am having issues of efficiency of backing up data from one SQL data
> base to another.
> The two servers in questions are on different networks , behind
> different firewalls. We have MS SQL 2000.
> On the source data i run a job with the following steps:
> 1> take trans backup every 4 hrs
> 2> ftp to the remote server
> 3> if ftp fails , disable the whole job
> On the target server I run a job which does the following
> 1> restore the trans backup with NORECOVERY.
> If the job fails at target. I will have to go through the whole process
> of doing a complete backup of the source , restoring it at the other
> ens and then starting trans-backup again.
> Also, if we do a failover to the target server, then when we roll back
> to the source server again we have to da a back-up of the target and
> restore it on the source server.
> Is ther a more efficent way of doing this'
>|||I suggest you try in a different forum for server or admin things.
David
"Vishal" <bajaj.vishal@.gmail.com> wrote in message
news:1145456687.737152.87130@.g10g2000cwb.googlegroups.com...
> Hi all,
> I am having issues of efficiency of backing up data from one SQL data
> base to another.
> The two servers in questions are on different networks , behind
> different firewalls. We have MS SQL 2000.
> On the source data i run a job with the following steps:
> 1> take trans backup every 4 hrs
> 2> ftp to the remote server
> 3> if ftp fails , disable the whole job
> On the target server I run a job which does the following
> 1> restore the trans backup with NORECOVERY.
> If the job fails at target. I will have to go through the whole process
> of doing a complete backup of the source , restoring it at the other
> ens and then starting trans-backup again.
> Also, if we do a failover to the target server, then when we roll back
> to the source server again we have to da a back-up of the target and
> restore it on the source server.
> Is ther a more efficent way of doing this'
>|||Thanks a lot. let me try somthing else here .

Efficient SQL Backup?

Hi all,
I am having issues of efficiency of backing up data from one SQL data
base to another.
The two servers in questions are on different networks , behind
different firewalls. We have MS SQL 2000.
On the source data i run a job with the following steps:
1> take trans backup every 4 hrs
2> ftp to the remote server
3> if ftp fails , disable the whole job
On the target server I run a job which does the following
1> restore the trans backup with NORECOVERY.
If the job fails at target. I will have to go through the whole process
of doing a complete backup of the source , restoring it at the other
ens and then starting trans-backup again.
Also, if we do a failover to the target server, then when we roll back
to the source server again we have to da a back-up of the target and
restore it on the source server.
Is ther a more efficent way of doing this'Actually, no. This is pretty much what Log Shipping does. If you failover
to the remote and then want to failback to the primary, then you have to get
all of the data from the remote to the primary which means a backup/restore
cycle.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
<bajaj.vishal@.gmail.com> wrote in message
news:1145456580.413687.286110@.t31g2000cwb.googlegroups.com...
> Hi all,
> I am having issues of efficiency of backing up data from one SQL data
> base to another.
> The two servers in questions are on different networks , behind
> different firewalls. We have MS SQL 2000.
> On the source data i run a job with the following steps:
> 1> take trans backup every 4 hrs
> 2> ftp to the remote server
> 3> if ftp fails , disable the whole job
> On the target server I run a job which does the following
> 1> restore the trans backup with NORECOVERY.
> If the job fails at target. I will have to go through the whole process
> of doing a complete backup of the source , restoring it at the other
> ens and then starting trans-backup again.
> Also, if we do a failover to the target server, then when we roll back
> to the source server again we have to da a back-up of the target and
> restore it on the source server.
> Is ther a more efficent way of doing this'
>

Efficient SQL Backup?

Hi all,

I am having issues of efficiency of backing up data from one SQL data
base to another.

The two servers in questions are on different networks , behind
different firewalls. We have MS SQL 2000.

On the source data i run a job with the following steps:

1> take trans backup every 4 hrs
2> ftp to the remote server
3> if ftp fails , disable the whole job

On the target server I run a job which does the following

1> restore the trans backup with NORECOVERY.

If the job fails at target. I will have to go through the whole process
of doing a complete backup of the source , restoring it at the other
ens and then starting trans-backup again.

Also, if we do a failover to the target server, then when we roll back
to the source server again we have to da a back-up of the target and
restore it on the source server.

Is ther a more efficent way of doing this??Vishal (bajaj.vishal@.gmail.com) writes:
> I am having issues of efficiency of backing up data from one SQL data
> base to another.
> The two servers in questions are on different networks , behind
> different firewalls. We have MS SQL 2000.
> On the source data i run a job with the following steps:
> 1> take trans backup every 4 hrs
> 2> ftp to the remote server
> 3> if ftp fails , disable the whole job
> On the target server I run a job which does the following
> 1> restore the trans backup with NORECOVERY.
> If the job fails at target. I will have to go through the whole process
> of doing a complete backup of the source , restoring it at the other
> ens and then starting trans-backup again.
> Also, if we do a failover to the target server, then when we roll back
> to the source server again we have to da a back-up of the target and
> restore it on the source server.
> Is ther a more efficent way of doing this??

I'm not sure exactly the purpose of this home-made log shipping is,
but could replication be a better alternative?

(Although, I have no idea whether replication can cross your network
and firewalls.)

--
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|||As per my understanding you are concerned with

3> if ftp fails , disable the whole job

* If you want to save time then you can go ahead and use SQL Litespeed
tool.

You can go through this article for more information.
http://www.sql-server-performance.c...d_spotlight.asp

Thanks
Ajay Rengunthwar (Ajay)|||thnks a lot, but will i be able to do a failover easily ? and can i do
transactional backups and keep restoring it at the other end. I cannot
do a complete backup due to bandwidth constraints.

efficient select

It seems I should be able to do 1 select and both return that
recordset and be able to set a variable from that recordset.

eg.
Declare @.refid int
Select t.* from mytable as t --return the recordset
Set @.refid = t.refid

the above doesn't work-how can I do it without making a second trip to
the database?
Thanks,
Rick"Rick" <rick@.abasoftware.com> wrote in message
news:28d7cbb9.0409140717.9d794dc@.posting.google.co m...
> It seems I should be able to do 1 select and both return that
> recordset and be able to set a variable from that recordset.
> eg.
> Declare @.refid int
> Select t.* from mytable as t --return the recordset
> Set @.refid = t.refid
> the above doesn't work-how can I do it without making a second trip to
> the database?
> Thanks,
> Rick

select @.refid = t.refid from mytable where <your condition|||"strider5" <strider5@.s.zm.com> wrote in message
news:ci73p5$l4a$1@.domitilla.aioe.org...
> "Rick" <rick@.abasoftware.com> wrote in message
> news:28d7cbb9.0409140717.9d794dc@.posting.google.co m...
> > It seems I should be able to do 1 select and both return that
> > recordset and be able to set a variable from that recordset.
> > eg.
> > Declare @.refid int
> > Select t.* from mytable as t --return the recordset
> > Set @.refid = t.refid
> > the above doesn't work-how can I do it without making a second trip to
> > the database?
> > Thanks,
> > Rick
> select @.refid = t.refid from mytable where <your condition>

should be :
select @.refid = t.refid from mytable as t where <your condition|||Rick (rick@.abasoftware.com) writes:
> It seems I should be able to do 1 select and both return that
> recordset and be able to set a variable from that recordset.
> eg.
> Declare @.refid int
> Select t.* from mytable as t --return the recordset
> Set @.refid = t.refid
> the above doesn't work-how can I do it without making a second trip to
> the database?

You can't both return a result set and set a variable in the same
SELECT statement.

In many cases, it is not very ineffecient to access the table twice,
as the second read will be from cache. But if the search conditions
calls for a longer execution time, one possibility is to buffer the
result in a temp table or table variable, and then access that table
twice.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Efficient record storage 10+ Million records

I have a database that stores survey information. I've been asked to add
information to it to test scalability. Well, its' hurting.
What I have is a 'sample' table or the people that took the survey and then
several related tables.
Lets' say the sample table looks something like
tblData_Sample
SampleID INT IDENTITY(1,1)
FullName VARCHAR(20)
AgeGroupID INT
IncomeID INT
With a clustered index on SampleID and Indexes on AgeGroupID and IncomeID
I also have a table for storing the survey responses. Right now its' setup
like
tblData_Grid
SampleID INT
RestaurantID INT
QuestionID INT
Response INT
There is also a table for storing complete information
tblData_Session
SampleID INT
StartDate DATETIME
EndDate DATETIME
With a clustered index on SampleID
With a clustered index on SampleID, RestaurantID, QuestionID and Indexes on
RestaurantID and QuestionID.
The problem I'm having is that after scaling out to only 93,000 completed
surveys, the data table is at 10,000,000 records.
Needless to say OUCH!!
Queries of the data table are painful... I've rebuilt all of my indexes, so
I know they're up-to-date and I'm kinda of stuck on what to do for speed
gains at this point.
It's taking way to long to process (10 seconds on the development server, I
haven't bothered trying the live cluster yet)
SELECT DISTINCT g.SampleID, g.RestaurantID
FROM tblData_Grid g
WHERE g.SampleID IN
(
SELECT s.SampleID
FROM tblData_Session s
WHERE s.EndDate IS NOT NULL
)
So what are things I can to do speed things up here? I've thought about
taking all questions that are asked of each respondent and moving them to a
horizontal table.
I've also considered using partitioned views. Perhaps store data in
mini-grid tables based on the sample quarter or restaurantid and then using
a partitioned view to simulate the grid table...
Before I start making any sweeping changes, I was hoping that someone here
could steer me in the right direction.
Thanks,
BenHi Ben
Have you considered trying a simple join rather than nested queries? e.g.
SELECT DISTINCT g.SampleID, g.RestaurantID
FROM tblData_Grid g
INNER JOIN tblData_Session s
ON (g.SampleID = s.SampleID)
WHERE (s.EndDate IS NOT NULL)
Also there is a question as to what you are actually using the data for and
how you intend to analyse it? The Design answers are very different
depending upon the uses. The query above is, I would guess, going to return
a massive amount of data. As I believe Mr William Vaughn (sir) is wont to
ask (dotNetRocks #100) - How small a font do you use to display that to your
users?
If what you want is to display a count of samples for each restaurant or
some such then test with that style of aggregate query. Also you presumably
have a separate table to map Restaurant IDs to their details and supporting
tables not mentioned.
Yours
Alasdair Russell
"Ben" wrote:

> I have a database that stores survey information. I've been asked to add
> information to it to test scalability. Well, its' hurting.
> What I have is a 'sample' table or the people that took the survey and the
n
> several related tables.
> Lets' say the sample table looks something like
> tblData_Sample
> SampleID INT IDENTITY(1,1)
> FullName VARCHAR(20)
> AgeGroupID INT
> IncomeID INT
> With a clustered index on SampleID and Indexes on AgeGroupID and IncomeID
> I also have a table for storing the survey responses. Right now its' setu
p
> like
> tblData_Grid
> SampleID INT
> RestaurantID INT
> QuestionID INT
> Response INT
> There is also a table for storing complete information
> tblData_Session
> SampleID INT
> StartDate DATETIME
> EndDate DATETIME
> With a clustered index on SampleID
> With a clustered index on SampleID, RestaurantID, QuestionID and Indexes o
n
> RestaurantID and QuestionID.
> The problem I'm having is that after scaling out to only 93,000 completed
> surveys, the data table is at 10,000,000 records.
> Needless to say OUCH!!
> Queries of the data table are painful... I've rebuilt all of my indexes,
so
> I know they're up-to-date and I'm kinda of stuck on what to do for speed
> gains at this point.
> It's taking way to long to process (10 seconds on the development server,
I
> haven't bothered trying the live cluster yet)
> SELECT DISTINCT g.SampleID, g.RestaurantID
> FROM tblData_Grid g
> WHERE g.SampleID IN
> (
> SELECT s.SampleID
> FROM tblData_Session s
> WHERE s.EndDate IS NOT NULL
> )
>
> So what are things I can to do speed things up here? I've thought about
> taking all questions that are asked of each respondent and moving them to
a
> horizontal table.
> I've also considered using partitioned views. Perhaps store data in
> mini-grid tables based on the sample quarter or restaurantid and then usin
g
> a partitioned view to simulate the grid table...
> Before I start making any sweeping changes, I was hoping that someone here
> could steer me in the right direction.
> Thanks,
> Ben
>
>|||Dhericean,
Thanks for the fast response. Yes, I've tried the join and believe it or
not its' taking slightly longer... Looking at the execution plan there is
an extra aggregation involved with the join and that's amounting to more
reads.
All of the supporting tables are present and exist in a similar fashion to
the ones below.
This query is not used to report data to the end user rather it is used to
preselect records ID's that match the users query. Then they go through the
system picking 'measures' and 'analysis groups' that calculate aggregated
stats for the records in the users initial query. I've found that staging
these ID's is better than running "select these records and process them",
the approach as it stands now says, "populate selection table, select
additional parameters, merge query onto selection table".
The end result populates a data table and a chart. Typically there are less
than ten data points per analysis. I hope this helps clear things up.
Maybe a better way to describe it is that the initial query defines the
subset of data to be viewed, and that query is cached to reduce its' impact
on subsequent requests where the data returned by the query is crunched,
efficivly seperating selection processing away from calculation processing.
Thanks,
Ben
"Dhericean" <Dhericean@.discussions.microsoft.com> wrote in message
news:B5C02FF4-E48B-4410-9C96-F9A1233F6138@.microsoft.com...
> Hi Ben
> Have you considered trying a simple join rather than nested queries? e.g.
> SELECT DISTINCT g.SampleID, g.RestaurantID
> FROM tblData_Grid g
> INNER JOIN tblData_Session s
> ON (g.SampleID = s.SampleID)
> WHERE (s.EndDate IS NOT NULL)
> Also there is a question as to what you are actually using the data for
> and
> how you intend to analyse it? The Design answers are very different
> depending upon the uses. The query above is, I would guess, going to
> return
> a massive amount of data. As I believe Mr William Vaughn (sir) is wont to
> ask (dotNetRocks #100) - How small a font do you use to display that to
> your
> users?
> If what you want is to display a count of samples for each restaurant or
> some such then test with that style of aggregate query. Also you
> presumably
> have a separate table to map Restaurant IDs to their details and
> supporting
> tables not mentioned.
> Yours
> Alasdair Russell
> "Ben" wrote:
>|||How about;
select distinct g.sampleid, g.RestaurantID
FROM tblData_Grid g
WHERE exists (SELECT s.SampleID
FROM tblData_Session s
WHERE s.sampleid = g.sampleid and
s.EndDate IS NOT NULL)
"Ben" wrote:

> I have a database that stores survey information. I've been asked to add
> information to it to test scalability. Well, its' hurting.
> What I have is a 'sample' table or the people that took the survey and the
n
> several related tables.
> Lets' say the sample table looks something like
> tblData_Sample
> SampleID INT IDENTITY(1,1)
> FullName VARCHAR(20)
> AgeGroupID INT
> IncomeID INT
> With a clustered index on SampleID and Indexes on AgeGroupID and IncomeID
> I also have a table for storing the survey responses. Right now its' setu
p
> like
> tblData_Grid
> SampleID INT
> RestaurantID INT
> QuestionID INT
> Response INT
> There is also a table for storing complete information
> tblData_Session
> SampleID INT
> StartDate DATETIME
> EndDate DATETIME
> With a clustered index on SampleID
> With a clustered index on SampleID, RestaurantID, QuestionID and Indexes o
n
> RestaurantID and QuestionID.
> The problem I'm having is that after scaling out to only 93,000 completed
> surveys, the data table is at 10,000,000 records.
> Needless to say OUCH!!
> Queries of the data table are painful... I've rebuilt all of my indexes,
so
> I know they're up-to-date and I'm kinda of stuck on what to do for speed
> gains at this point.
> It's taking way to long to process (10 seconds on the development server,
I
> haven't bothered trying the live cluster yet)
> SELECT DISTINCT g.SampleID, g.RestaurantID
> FROM tblData_Grid g
> WHERE g.SampleID IN
> (
> SELECT s.SampleID
> FROM tblData_Session s
> WHERE s.EndDate IS NOT NULL
> )
>
> So what are things I can to do speed things up here? I've thought about
> taking all questions that are asked of each respondent and moving them to
a
> horizontal table.
> I've also considered using partitioned views. Perhaps store data in
> mini-grid tables based on the sample quarter or restaurantid and then usin
g
> a partitioned view to simulate the grid table...
> Before I start making any sweeping changes, I was hoping that someone here
> could steer me in the right direction.
> Thanks,
> Ben
>
>

Efficient Query

Hi, I need an expert help on this, i have this query, but it seems to
trigger the parallelism on execution, i want to get around that and have a
more efficient query, can some please help me with this query. thank you in
advance.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SELECT DISTINCT
ProductOrder.*,
Agency.AgencyName,
zStatus.Status,
zStatus.StatusID,
Asset.AssetTypeID
FROM
ProductOrder
INNER JOIN
[User] ON ProductOrder.UserGUID = [User].UserGUID
LEFT OUTER JOIN
GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
LEFT OUTER JOIN
[Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
LEFT OUTER JOIN
Agency ON [Group].AgencyGUID = Agency.AgencyGUID
INNER JOIN
ProductOrderItem on ProductOrder.ProductOrderGUID = ProductOrderItem.ProductOrderGUID
INNER JOIN
zStatus on ProductOrderItem.Status = zStatus.StatusID
LEFT OUTER JOIN
Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
WHERE
zStatus.StatusOrder IN
(SELECT
MIN(zStatus.StatusOrder)
FROM
ProductOrderItem
INNER JOIN
zStatus ON ProductOrderItem.Status = zStatus.StatusID
WHERE
ProductOrderItem.ProductOrderGUID = ProductOrder.ProductOrderGUID)
AND
ProductOrder.OrderTypeID = 4
AND
zStatus.StatusCompleted = 0
AND
ProductOrder.IsBasket = 0
AND
zStatus.StatusID <> 47
ORDER BY
DateCreated ASCJust a quick note..
You can set the "Cost Threshold for Parallelism" option to a higher value to
essentially turn off parallel query execution.
You can set it from 0 - 32k. The default is 5.
You may want to read up on the affinity mask (if you are using multiple
processors) and the max degree of parallelism option which can affect the
Cost Threshold.
Rick
"Daniel" <danielk@.adstream.com.au> wrote in message
news:%23wVcefujEHA.2788@.tk2msftngp13.phx.gbl...
> Hi, I need an expert help on this, i have this query, but it seems to
> trigger the parallelism on execution, i want to get around that and have a
> more efficient query, can some please help me with this query. thank you
in
> advance.
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> SET NOCOUNT ON
> SELECT DISTINCT
> ProductOrder.*,
> Agency.AgencyName,
> zStatus.Status,
> zStatus.StatusID,
> Asset.AssetTypeID
> FROM
> ProductOrder
> INNER JOIN
> [User] ON ProductOrder.UserGUID = [User].UserGUID
> LEFT OUTER JOIN
> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
> LEFT OUTER JOIN
> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
> LEFT OUTER JOIN
> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
> INNER JOIN
> ProductOrderItem on ProductOrder.ProductOrderGUID => ProductOrderItem.ProductOrderGUID
> INNER JOIN
> zStatus on ProductOrderItem.Status = zStatus.StatusID
> LEFT OUTER JOIN
> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
> WHERE
> zStatus.StatusOrder IN
> (SELECT
> MIN(zStatus.StatusOrder)
> FROM
> ProductOrderItem
> INNER JOIN
> zStatus ON ProductOrderItem.Status = zStatus.StatusID
> WHERE
> ProductOrderItem.ProductOrderGUID => ProductOrder.ProductOrderGUID)
> AND
> ProductOrder.OrderTypeID = 4
> AND
> zStatus.StatusCompleted = 0
> AND
> ProductOrder.IsBasket = 0
> AND
> zStatus.StatusID <> 47
> ORDER BY
> DateCreated ASC
>|||Daniel wrote:
> Hi, I need an expert help on this, i have this query, but it seems to
> trigger the parallelism on execution, i want to get around that and
> have a more efficient query, can some please help me with this query.
> thank you in advance.
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> SET NOCOUNT ON
> SELECT DISTINCT
> ProductOrder.*,
> Agency.AgencyName,
> zStatus.Status,
> zStatus.StatusID,
> Asset.AssetTypeID
> FROM
> ProductOrder
> INNER JOIN
> [User] ON ProductOrder.UserGUID = [User].UserGUID
> LEFT OUTER JOIN
> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
> LEFT OUTER JOIN
> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
> LEFT OUTER JOIN
> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
> INNER JOIN
> ProductOrderItem on ProductOrder.ProductOrderGUID => ProductOrderItem.ProductOrderGUID
> INNER JOIN
> zStatus on ProductOrderItem.Status = zStatus.StatusID
> LEFT OUTER JOIN
> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
> WHERE
> zStatus.StatusOrder IN
> (SELECT
> MIN(zStatus.StatusOrder)
> FROM
> ProductOrderItem
> INNER JOIN
> zStatus ON ProductOrderItem.Status => zStatus.StatusID WHERE
> ProductOrderItem.ProductOrderGUID => ProductOrder.ProductOrderGUID)
> AND
> ProductOrder.OrderTypeID = 4
> AND
> zStatus.StatusCompleted = 0
> AND
> ProductOrder.IsBasket = 0
> AND
> zStatus.StatusID <> 47
> ORDER BY
> DateCreated ASC
If you add a MAXDOP 1 to the query, it will only use one processor.
--
David G.|||thanks for the reply. But as far as i know, setting the cost threshold would
have set from the server. I only want to make changes to this query.
I don't want to have the parallelism because, the result of the query is
quite small, probably less than 20 rows. that's why, I think, this
paralellism is quite expensive for this query. Do you think the query can be
made more efficient, or is it already as efficient as it can be..' thank
you.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:Ovg6youjEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Just a quick note..
> You can set the "Cost Threshold for Parallelism" option to a higher value
> to
> essentially turn off parallel query execution.
> You can set it from 0 - 32k. The default is 5.
>
> You may want to read up on the affinity mask (if you are using multiple
> processors) and the max degree of parallelism option which can affect the
> Cost Threshold.
>
> Rick
>
> "Daniel" <danielk@.adstream.com.au> wrote in message
> news:%23wVcefujEHA.2788@.tk2msftngp13.phx.gbl...
>> Hi, I need an expert help on this, i have this query, but it seems to
>> trigger the parallelism on execution, i want to get around that and have
>> a
>> more efficient query, can some please help me with this query. thank you
> in
>> advance.
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> SET NOCOUNT ON
>> SELECT DISTINCT
>> ProductOrder.*,
>> Agency.AgencyName,
>> zStatus.Status,
>> zStatus.StatusID,
>> Asset.AssetTypeID
>> FROM
>> ProductOrder
>> INNER JOIN
>> [User] ON ProductOrder.UserGUID = [User].UserGUID
>> LEFT OUTER JOIN
>> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
>> LEFT OUTER JOIN
>> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
>> LEFT OUTER JOIN
>> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
>> INNER JOIN
>> ProductOrderItem on ProductOrder.ProductOrderGUID =>> ProductOrderItem.ProductOrderGUID
>> INNER JOIN
>> zStatus on ProductOrderItem.Status = zStatus.StatusID
>> LEFT OUTER JOIN
>> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
>> WHERE
>> zStatus.StatusOrder IN
>> (SELECT
>> MIN(zStatus.StatusOrder)
>> FROM
>> ProductOrderItem
>> INNER JOIN
>> zStatus ON ProductOrderItem.Status = zStatus.StatusID
>> WHERE
>> ProductOrderItem.ProductOrderGUID =>> ProductOrder.ProductOrderGUID)
>> AND
>> ProductOrder.OrderTypeID = 4
>> AND
>> zStatus.StatusCompleted = 0
>> AND
>> ProductOrder.IsBasket = 0
>> AND
>> zStatus.StatusID <> 47
>> ORDER BY
>> DateCreated ASC
>>
>|||it doesn't really help, it even make it worse... well, what i'm trying to
reduce is the subtree cost... right now, my subtree cost is 110...
that's way too high... that's because i have a distinct. if i take of the
distinct, it still cost me 31... i'm trying to get it down as low as 10...
anyone can help me with this..'
"David G." <david_nospam@.nospam.com> wrote in message
news:eKXapvujEHA.2680@.TK2MSFTNGP15.phx.gbl...
> Daniel wrote:
>> Hi, I need an expert help on this, i have this query, but it seems to
>> trigger the parallelism on execution, i want to get around that and
>> have a more efficient query, can some please help me with this query.
>> thank you in advance.
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> SET NOCOUNT ON
>> SELECT DISTINCT
>> ProductOrder.*,
>> Agency.AgencyName,
>> zStatus.Status,
>> zStatus.StatusID,
>> Asset.AssetTypeID
>> FROM
>> ProductOrder
>> INNER JOIN
>> [User] ON ProductOrder.UserGUID = [User].UserGUID
>> LEFT OUTER JOIN
>> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
>> LEFT OUTER JOIN
>> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
>> LEFT OUTER JOIN
>> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
>> INNER JOIN
>> ProductOrderItem on ProductOrder.ProductOrderGUID =>> ProductOrderItem.ProductOrderGUID
>> INNER JOIN
>> zStatus on ProductOrderItem.Status = zStatus.StatusID
>> LEFT OUTER JOIN
>> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
>> WHERE
>> zStatus.StatusOrder IN
>> (SELECT
>> MIN(zStatus.StatusOrder)
>> FROM
>> ProductOrderItem
>> INNER JOIN
>> zStatus ON ProductOrderItem.Status =>> zStatus.StatusID WHERE
>> ProductOrderItem.ProductOrderGUID =>> ProductOrder.ProductOrderGUID)
>> AND
>> ProductOrder.OrderTypeID = 4
>> AND
>> zStatus.StatusCompleted = 0
>> AND
>> ProductOrder.IsBasket = 0
>> AND
>> zStatus.StatusID <> 47
>> ORDER BY
>> DateCreated ASC
> If you add a MAXDOP 1 to the query, it will only use one processor.
> --
> David G.|||Daniel wrote:
> it doesn't really help, it even make it worse... well, what i'm
> trying to reduce is the subtree cost... right now, my subtree cost
> is 110...
> that's way too high... that's because i have a distinct. if i take
> of the distinct, it still cost me 31... i'm trying to get it down as
> low as 10... anyone can help me with this..'
>
> "David G." <david_nospam@.nospam.com> wrote in message
> news:eKXapvujEHA.2680@.TK2MSFTNGP15.phx.gbl...
>> Daniel wrote:
>> Hi, I need an expert help on this, i have this query, but it seems
>> to trigger the parallelism on execution, i want to get around that
>> and have a more efficient query, can some please help me with this
>> query. thank you in advance.
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> SET NOCOUNT ON
>> SELECT DISTINCT
>> ProductOrder.*,
>> Agency.AgencyName,
>> zStatus.Status,
>> zStatus.StatusID,
>> Asset.AssetTypeID
>> FROM
>> ProductOrder
>> INNER JOIN
>> [User] ON ProductOrder.UserGUID = [User].UserGUID
>> LEFT OUTER JOIN
>> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
>> LEFT OUTER JOIN
>> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
>> LEFT OUTER JOIN
>> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
>> INNER JOIN
>> ProductOrderItem on ProductOrder.ProductOrderGUID =>> ProductOrderItem.ProductOrderGUID
>> INNER JOIN
>> zStatus on ProductOrderItem.Status = zStatus.StatusID
>> LEFT OUTER JOIN
>> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
>> WHERE
>> zStatus.StatusOrder IN
>> (SELECT
>> MIN(zStatus.StatusOrder)
>> FROM
>> ProductOrderItem
>> INNER JOIN
>> zStatus ON ProductOrderItem.Status =>> zStatus.StatusID WHERE
>> ProductOrderItem.ProductOrderGUID =>> ProductOrder.ProductOrderGUID)
>> AND
>> ProductOrder.OrderTypeID = 4
>> AND
>> zStatus.StatusCompleted = 0
>> AND
>> ProductOrder.IsBasket = 0
>> AND
>> zStatus.StatusID <> 47
>> ORDER BY
>> DateCreated ASC
>> If you add a MAXDOP 1 to the query, it will only use one processor.
>> --
>> David G.
You said in your original post you want to get rid of the parallelism.
Are you saying that what you really want is the query to run more
efficiently? If so, try either reducing the number of tables (joins) in
the query. Also try removing the zStatus.StatusOrder IN subselect. It
looks like this subselect returns one value using the MIN(). if so, try
grabbing that value first and building the query using sp_executesql
since local variables used as bind variables in a stored procedure may
not be optimized well by SQL Server.
Also, what are the performance stats for the query? What amount of CPU
is used? How many reads? Are table scans being performed? If so, on what
tables? Are indexes in place to prevent the scan operations?
David G.|||> You said in your original post you want to get rid of the parallelism.
> Are you saying that what you really want is the query to run more
> efficiently? If so, try either reducing the number of tables (joins) in
> the query. Also try removing the zStatus.StatusOrder IN subselect. It
> looks like this subselect returns one value using the MIN(). if so, try
> grabbing that value first and building the query using sp_executesql
> since local variables used as bind variables in a stored procedure may
> not be optimized well by SQL Server.
> Also, what are the performance stats for the query? What amount of CPU
> is used? How many reads? Are table scans being performed? If so, on what
> tables? Are indexes in place to prevent the scan operations?
how can i see all those things...' performance stats, cpu used, reads..
the estimate row counts is 35,700 and the subtreecost is 110.
i don't have any table scan as everything is using either clustered index
scan or index seek.
it looks quite efficient, but it's still expensive. is there any other way
to optimize the query..'
the process that is most expensive is the distinct, but i cannot live
without it...
and i can't remove the zstatusorder min(), as it is checking the the lowest
status of the productorderitem of each productorder
and i don't think i can reduce the tables coz i need all of them...
"David G." <david_nospam@.nospam.com> wrote in message
news:uQagcYxjEHA.1040@.TK2MSFTNGP10.phx.gbl...
> Daniel wrote:
>> it doesn't really help, it even make it worse... well, what i'm
>> trying to reduce is the subtree cost... right now, my subtree cost
>> is 110...
>> that's way too high... that's because i have a distinct. if i take
>> of the distinct, it still cost me 31... i'm trying to get it down as
>> low as 10... anyone can help me with this..'
>>
>> "David G." <david_nospam@.nospam.com> wrote in message
>> news:eKXapvujEHA.2680@.TK2MSFTNGP15.phx.gbl...
>> Daniel wrote:
>> Hi, I need an expert help on this, i have this query, but it seems
>> to trigger the parallelism on execution, i want to get around that
>> and have a more efficient query, can some please help me with this
>> query. thank you in advance.
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> SET NOCOUNT ON
>> SELECT DISTINCT
>> ProductOrder.*,
>> Agency.AgencyName,
>> zStatus.Status,
>> zStatus.StatusID,
>> Asset.AssetTypeID
>> FROM
>> ProductOrder
>> INNER JOIN
>> [User] ON ProductOrder.UserGUID = [User].UserGUID
>> LEFT OUTER JOIN
>> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
>> LEFT OUTER JOIN
>> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
>> LEFT OUTER JOIN
>> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
>> INNER JOIN
>> ProductOrderItem on ProductOrder.ProductOrderGUID =>> ProductOrderItem.ProductOrderGUID
>> INNER JOIN
>> zStatus on ProductOrderItem.Status = zStatus.StatusID
>> LEFT OUTER JOIN
>> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
>> WHERE
>> zStatus.StatusOrder IN
>> (SELECT
>> MIN(zStatus.StatusOrder)
>> FROM
>> ProductOrderItem
>> INNER JOIN
>> zStatus ON ProductOrderItem.Status =>> zStatus.StatusID WHERE
>> ProductOrderItem.ProductOrderGUID =>> ProductOrder.ProductOrderGUID)
>> AND
>> ProductOrder.OrderTypeID = 4
>> AND
>> zStatus.StatusCompleted = 0
>> AND
>> ProductOrder.IsBasket = 0
>> AND
>> zStatus.StatusID <> 47
>> ORDER BY
>> DateCreated ASC
>> If you add a MAXDOP 1 to the query, it will only use one processor.
>> --
>> David G.
> You said in your original post you want to get rid of the parallelism.
> Are you saying that what you really want is the query to run more
> efficiently? If so, try either reducing the number of tables (joins) in
> the query. Also try removing the zStatus.StatusOrder IN subselect. It
> looks like this subselect returns one value using the MIN(). if so, try
> grabbing that value first and building the query using sp_executesql
> since local variables used as bind variables in a stored procedure may
> not be optimized well by SQL Server.
> Also, what are the performance stats for the query? What amount of CPU
> is used? How many reads? Are table scans being performed? If so, on what
> tables? Are indexes in place to prevent the scan operations?
>
> --
> David G.
>|||On Tue, 31 Aug 2004 09:19:56 +1000, "Daniel" <danielk@.adstream.com.au>
wrote:
>Hi, I need an expert help on this, i have this query, but it seems to
>trigger the parallelism on execution, i want to get around that and have a
>more efficient query, can some please help me with this query. thank you in
>advance.
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> SET NOCOUNT ON
> SELECT DISTINCT
> ProductOrder.*,
I'm no expert, but I read loads of times not to use * in the select on
production server. So thats the advice I'll offer here.
Name the ProductOrder columns you want (even if theres 50 of them).
HTH
al.
> Agency.AgencyName,
> zStatus.Status,
> zStatus.StatusID,
> Asset.AssetTypeID
> FROM
> ProductOrder
> INNER JOIN
> [User] ON ProductOrder.UserGUID = [User].UserGUID
> LEFT OUTER JOIN
> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
> LEFT OUTER JOIN
> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
> LEFT OUTER JOIN
> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
> INNER JOIN
> ProductOrderItem on ProductOrder.ProductOrderGUID =>ProductOrderItem.ProductOrderGUID
> INNER JOIN
> zStatus on ProductOrderItem.Status = zStatus.StatusID
> LEFT OUTER JOIN
> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
> WHERE
> zStatus.StatusOrder IN
> (SELECT
> MIN(zStatus.StatusOrder)
> FROM
> ProductOrderItem
> INNER JOIN
> zStatus ON ProductOrderItem.Status = zStatus.StatusID
> WHERE
> ProductOrderItem.ProductOrderGUID =>ProductOrder.ProductOrderGUID)
> AND
> ProductOrder.OrderTypeID = 4
> AND
> zStatus.StatusCompleted = 0
> AND
> ProductOrder.IsBasket = 0
> AND
> zStatus.StatusID <> 47
>ORDER BY
> DateCreated ASC
>|||On Tue, 31 Aug 2004 17:05:39 +1000, Daniel wrote:
>> You said in your original post you want to get rid of the parallelism.
>> Are you saying that what you really want is the query to run more
>> efficiently? If so, try either reducing the number of tables (joins) in
>> the query. Also try removing the zStatus.StatusOrder IN subselect. It
>> looks like this subselect returns one value using the MIN(). if so, try
>> grabbing that value first and building the query using sp_executesql
>> since local variables used as bind variables in a stored procedure may
>> not be optimized well by SQL Server.
>> Also, what are the performance stats for the query? What amount of CPU
>> is used? How many reads? Are table scans being performed? If so, on what
>> tables? Are indexes in place to prevent the scan operations?
>how can i see all those things...' performance stats, cpu used, reads..
>the estimate row counts is 35,700 and the subtreecost is 110.
>i don't have any table scan as everything is using either clustered index
>scan or index seek.
>it looks quite efficient, but it's still expensive. is there any other way
>to optimize the query..'
>the process that is most expensive is the distinct, but i cannot live
>without it...
>and i can't remove the zstatusorder min(), as it is checking the the lowest
>status of the productorderitem of each productorder
>and i don't think i can reduce the tables coz i need all of them...
Hi Daniel,
A clustered index IS the table, so if you get a clustered index scan, you
have a table scan. There might be room for improvement.
See if supplying a column list instead of SELECT * (as suggested by Harag)
helps. You might also try if it replacing StatusOrder IN (subselect) by
StatusOrder = (subselect) helps. The subselect will never return more than
one value, so they should be equivalent - maybe = instead of IN will give
the optimizer new ideas.
For further help, you'll have to provide more information: the design of
your tables (CREATE TABLE statements, including all constraints and all
indexes but excluding irrelevant columns), some sample data (as INSERT
statements, so we don't have to type them ourselves <g>) and expected
output based on the sample data. Plus a description of the business
problem.
See http://www.aspfaq.com/etiquette.asp?id=5006
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Daniel,
Don't focus too much on an individual 'expensive' part of the query
plan. In the end, the only thing that matters is the total elapsed time
of the query. Proper indexing and rewriting the query can dramatically
change the optimizer strategy (= query plan).
It looks like you can remove your IN by rewriting it as below. Of
course, you need to test if the results are still correct.
set quoted_identifier on
SELECT DISTINCT
ProductOrder.*,
Agency.AgencyName,
zStatus.Status,
zStatus.StatusID,
Asset.AssetTypeID
FROM ProductOrder
INNER JOIN ProductOrderItem ON ProductOrder.ProductOrderGUID =ProductOrderItem.ProductOrderGUID
INNER JOIN zStatus ON ProductOrderItem.Status =zStatus.StatusID
INNER JOIN "User" ON ProductOrder.UserGUID ="User".UserGUID
LEFT JOIN GroupMapping ON "User".UserGUID =GroupMapping.UserGUID
LEFT JOIN "Group" ON GroupMapping.GroupGUID ="Group".GroupGUID
LEFT JOIN Agency ON "Group".AgencyGUID =Agency.AgencyGUID
LEFT JOIN Asset ON Asset.AssetGUID =ProductOrderItem.AssetGUID
WHERE zStatus.StatusOrder = (
SELECT MIN(zStatus.StatusOrder)
FROM ProductOrderItem
INNER JOIN zStatus ON ProductOrderItem.Status =zStatus.StatusID
WHERE ProductOrderItem.ProductOrderGUID =ProductOrder.ProductOrderGUID
)
AND ProductOrder.OrderTypeID = 4
AND zStatus.StatusCompleted = 0
AND ProductOrder.IsBasket = 0
AND zStatus.StatusID <> 47
ORDER BY DateCreated ASC
If you expect just one row for each combination of
(ProductOrder.*,zStatus.Status,Status.StatusID), then you can remove
DISTINCT by rewriting the LEFT OUTER JOINs as scalar subqueries.
For example, you can rewrite
SELECT DISTINCT ... , Asset.AssetTypeID
FROM ...
LEFT JOIN Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
as
SELECT ... , (
SELECT AssetTypeID
FROM Asset
WHERE Asset.AssetGUID = ProductOrderItem.AssetGUID
) AS AssetTypeID
FROM ...
Hope this helps,
Gert-Jan
Daniel wrote:
> it doesn't really help, it even make it worse... well, what i'm trying to
> reduce is the subtree cost... right now, my subtree cost is 110...
> that's way too high... that's because i have a distinct. if i take of the
> distinct, it still cost me 31... i'm trying to get it down as low as 10...
> anyone can help me with this..'
> "David G." <david_nospam@.nospam.com> wrote in message
> news:eKXapvujEHA.2680@.TK2MSFTNGP15.phx.gbl...
> > Daniel wrote:
> >> Hi, I need an expert help on this, i have this query, but it seems to
> >> trigger the parallelism on execution, i want to get around that and
> >> have a more efficient query, can some please help me with this query.
> >> thank you in advance.
> >>
> >> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> >> SET NOCOUNT ON
> >>
> >> SELECT DISTINCT
> >> ProductOrder.*,
> >> Agency.AgencyName,
> >> zStatus.Status,
> >> zStatus.StatusID,
> >> Asset.AssetTypeID
> >> FROM
> >> ProductOrder
> >> INNER JOIN
> >> [User] ON ProductOrder.UserGUID = [User].UserGUID
> >> LEFT OUTER JOIN
> >> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
> >> LEFT OUTER JOIN
> >> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
> >> LEFT OUTER JOIN
> >> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
> >> INNER JOIN
> >> ProductOrderItem on ProductOrder.ProductOrderGUID => >> ProductOrderItem.ProductOrderGUID
> >> INNER JOIN
> >> zStatus on ProductOrderItem.Status = zStatus.StatusID
> >> LEFT OUTER JOIN
> >> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
> >> WHERE
> >> zStatus.StatusOrder IN
> >> (SELECT
> >> MIN(zStatus.StatusOrder)
> >> FROM
> >> ProductOrderItem
> >> INNER JOIN
> >> zStatus ON ProductOrderItem.Status => >> zStatus.StatusID WHERE
> >> ProductOrderItem.ProductOrderGUID => >> ProductOrder.ProductOrderGUID)
> >> AND
> >> ProductOrder.OrderTypeID = 4
> >> AND
> >> zStatus.StatusCompleted = 0
> >> AND
> >> ProductOrder.IsBasket = 0
> >> AND
> >> zStatus.StatusID <> 47
> >> ORDER BY
> >> DateCreated ASC
> >
> > If you add a MAXDOP 1 to the query, it will only use one processor.
> >
> > --
> > David G.
--
(Please reply only to the newsgroup)|||Gert-Jan Strik wrote:
> Hi Daniel,
> Don't focus too much on an individual 'expensive' part of the query
> plan. In the end, the only thing that matters is the total elapsed
> time of the query. Proper indexing and rewriting the query can
> dramatically change the optimizer strategy (= query plan).
>
> It looks like you can remove your IN by rewriting it as below. Of
> course, you need to test if the results are still correct.
> set quoted_identifier on
> SELECT DISTINCT
> ProductOrder.*,
> Agency.AgencyName,
> zStatus.Status,
> zStatus.StatusID,
> Asset.AssetTypeID
> FROM ProductOrder
> INNER JOIN ProductOrderItem ON ProductOrder.ProductOrderGUID => ProductOrderItem.ProductOrderGUID
> INNER JOIN zStatus ON ProductOrderItem.Status => zStatus.StatusID
> INNER JOIN "User" ON ProductOrder.UserGUID => "User".UserGUID
> LEFT JOIN GroupMapping ON "User".UserGUID => GroupMapping.UserGUID
> LEFT JOIN "Group" ON GroupMapping.GroupGUID => "Group".GroupGUID
> LEFT JOIN Agency ON "Group".AgencyGUID => Agency.AgencyGUID
> LEFT JOIN Asset ON Asset.AssetGUID => ProductOrderItem.AssetGUID
> WHERE zStatus.StatusOrder = (
> SELECT MIN(zStatus.StatusOrder)
> FROM ProductOrderItem
> INNER JOIN zStatus ON ProductOrderItem.Status => zStatus.StatusID
> WHERE ProductOrderItem.ProductOrderGUID => ProductOrder.ProductOrderGUID
> )
> AND ProductOrder.OrderTypeID = 4
> AND zStatus.StatusCompleted = 0
> AND ProductOrder.IsBasket = 0
> AND zStatus.StatusID <> 47
> ORDER BY DateCreated ASC
>
> If you expect just one row for each combination of
> (ProductOrder.*,zStatus.Status,Status.StatusID), then you can remove
> DISTINCT by rewriting the LEFT OUTER JOINs as scalar subqueries.
> For example, you can rewrite
> SELECT DISTINCT ... , Asset.AssetTypeID
> FROM ...
> LEFT JOIN Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
> as
> SELECT ... , (
> SELECT AssetTypeID
> FROM Asset
> WHERE Asset.AssetGUID = ProductOrderItem.AssetGUID
> ) AS AssetTypeID
> FROM ...
> Hope this helps,
> Gert-Jan
>
> Daniel wrote:
>> it doesn't really help, it even make it worse... well, what i'm
>> trying to reduce is the subtree cost... right now, my subtree cost
>> is 110...
>> that's way too high... that's because i have a distinct. if i take
>> of the distinct, it still cost me 31... i'm trying to get it down
>> as low as 10... anyone can help me with this..'
>> "David G." <david_nospam@.nospam.com> wrote in message
>> news:eKXapvujEHA.2680@.TK2MSFTNGP15.phx.gbl...
>> Daniel wrote:
>> Hi, I need an expert help on this, i have this query, but it seems
>> to trigger the parallelism on execution, i want to get around that
>> and have a more efficient query, can some please help me with this
>> query. thank you in advance.
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> SET NOCOUNT ON
>> SELECT DISTINCT
>> ProductOrder.*,
>> Agency.AgencyName,
>> zStatus.Status,
>> zStatus.StatusID,
>> Asset.AssetTypeID
>> FROM
>> ProductOrder
>> INNER JOIN
>> [User] ON ProductOrder.UserGUID = [User].UserGUID
>> LEFT OUTER JOIN
>> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
>> LEFT OUTER JOIN
>> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
>> LEFT OUTER JOIN
>> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
>> INNER JOIN
>> ProductOrderItem on ProductOrder.ProductOrderGUID =>> ProductOrderItem.ProductOrderGUID
>> INNER JOIN
>> zStatus on ProductOrderItem.Status = zStatus.StatusID
>> LEFT OUTER JOIN
>> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
>> WHERE
>> zStatus.StatusOrder IN
>> (SELECT
>> MIN(zStatus.StatusOrder)
>> FROM
>> ProductOrderItem
>> INNER JOIN
>> zStatus ON ProductOrderItem.Status =>> zStatus.StatusID WHERE
>> ProductOrderItem.ProductOrderGUID =>> ProductOrder.ProductOrderGUID)
>> AND
>> ProductOrder.OrderTypeID = 4
>> AND
>> zStatus.StatusCompleted = 0
>> AND
>> ProductOrder.IsBasket = 0
>> AND
>> zStatus.StatusID <> 47
>> ORDER BY
>> DateCreated ASC
>> If you add a MAXDOP 1 to the query, it will only use one processor.
>> --
>> David G.
And I would add that you should eliminate any Order By clauses from
production queries unless the application processing the returned data
absolutely needs the information returned sorted. Most apps either won't
care about the order of the rows or will use some type of grid control
that can be easily sorted on the client. Sorting results just adds
overhead to a query.
--
David G.|||thanks guys, much appreciated for your reply.
i've changed the 'in' to '=' and the have changed the *, but it doesn't help
much.
well, i'm handing that query over to my guru to check. i'll let you know
after he finished optimizing it.
cheers,
Daniel R. Kusnadi

Friday, March 9, 2012

email address domain strip of query

Friday, February 24, 2012

Efficient INSERT of rows- .NET

Sunday, February 19, 2012

Efficient way to transfer huge amount of records

Efficient way to store game data: help?

efficient way to put multiple tables in one xml output

Efficient way than IN statement

Efficient way of purging old data?

Efficient table record size

efficient SQL profiling

Efficient SQL Backup?

Efficient SQL Backup?

Efficient SQL Backup?

Efficient SQL Backup?

efficient select

Efficient record storage 10+ Million records

Efficient Query

Efficient Query

Efficient Query

Sql Edition Selection

Blog Archive

About Me