performance statistics

Hello, I want to use a command line utility to run queries and gather
performance statistics on them. I would like to dump my query results
to nul because all I really care about is the running time. Does anyone
know how to get this using osql? The -p option prints to stdout along
with the query.
Something similar to the db2batch -o r 0
Thanks,
Jen
--
Posted via http://dbforums.comTry:
OSQL -E -S Myserver -i"MyScriptFile.sql" -o NUL
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"moxie" <member44687@.dbforums.com> wrote in message
news:3495957.1066436974@.dbforums.com...
> Hello, I want to use a command line utility to run queries and gather
> performance statistics on them. I would like to dump my query results
> to nul because all I really care about is the running time. Does
anyone
> know how to get this using osql? The -p option prints to stdout along
> with the query.
>
> Something similar to the db2batch -o r 0
>
> Thanks,
> Jen
>
> --
> Posted via http://dbforums.com|||This seams like a resonable approach, but -o nul dumps all of the output
including the performance statistics from -p. I want to keep the
performance statistics.
I guess I want something like setting rowcount 1, but this seems to only
process the query as a top 1 query and stops query processing when the
first tuple is output, especially when it is a projection.
Thanks,
Jen
Posted via http://dbforums.com|||If you simply want to ignore the query output, you might consider
selecting the results into variables like the example below. This will
provide OSQL metrics that don't include resultset processing
DECLARE @.MyData int
SELECT @.MyData = MyData FROM MyTable
There may be better ways to gather performance metrics, though. Can you
elaborate on your objectives?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"moxie" <member44687@.dbforums.com> wrote in message
news:3503080.1066686834@.dbforums.com...
> This seams like a resonable approach, but -o nul dumps all of the
output
> including the performance statistics from -p. I want to keep the
> performance statistics.
>
> I guess I want something like setting rowcount 1, but this seems to
only
> process the query as a top 1 query and stops query processing when the
> first tuple is output, especially when it is a projection.
>
> Thanks,
> Jen
>
> --
> Posted via http://dbforums.com

Performance problems with SQL commands in data flow task

SQL statement within an OLE DB Command component is extremely slow (hours, days). Same SQL statement executed within a query window of SQL Server Management Studio takes only a few seconds. Using a fairly simple SQL UPDATE statement against a table with only 21,000 rows. Query:

UPDATE Pearson_Load
SET Process_Flag = 'E',
Error_Msg = 'Error: Missing address elements Address_Line_1, City, and/or State'
WHERE (Address_Line_1 = ' '
OR City = ' '
OR State = ' ')
AND Process_Flag = ' '

Any suggestions on how to improve the performance of this task or an alternate solution are appreciated. Thank you.

Jeff-B wrote:

SQL statement within an OLE DB Command component is extremely slow (hours, days). Same SQL statement executed within a query window of SQL Server Management Studio takes only a few seconds. Using a fairly simple SQL UPDATE statement against a table with only 21,000 rows. Query:

UPDATE Pearson_Load
SET Process_Flag = 'E',
Error_Msg = 'Error: Missing address elements Address_Line_1, City, and/or State'
WHERE (Address_Line_1 = ' '
OR City = ' '
OR State = ' ')
AND Process_Flag = ' '

Any suggestions on how to improve the performance of this task or an alternate solution are appreciated. Thank you.

You should redirect those rows destined for update to a table and then use an Execute SQL task in the control flow to perform a set-based update. What you've got now is a new, distinct update command for every row on the update path. This is costly.|||

Thank you Phil! I just moved the queries (I actually had 4 separate queries) that I was executing as separate OLE DB Command components in the data flow task into an Execute SQL task in the control flow and the process ran in seconds. I don't think that is exactly what you meant, but I wasn't sure what you meant by the suggestion to "redirect those rows destined for update to a table and then use an Execute SQL task in the control flow to perform a set-based update".

If you have time to comment so I understand the problem correctly, what I was doing wrong by using a data flow task with a table as an OLE DB source was executing the SQL statement in each OLE DB Command component I defined 21,000 times - once for each row in the table. So instead of executing 4 distinct queries, I was really executing 84,000 queries. If that is the case, when is it OK (if ever) to use such a scenario? Should the SQL command being executed be defined to only work on the current table entry? What would the syntax look like?

|||

Jeff-B wrote:

Thank you Phil! I just moved the queries (I actually had 4 separate queries) that I was executing as separate OLE DB Command components in the data flow task into an Execute SQL task in the control flow and the process ran in seconds. I don't think that is exactly what you meant, but I wasn't sure what you meant by the suggestion to "redirect those rows destined for update to a table and then use an Execute SQL task in the control flow to perform a set-based update".

If you have time to comment so I understand the problem correctly, what I was doing wrong by using a data flow task with a table as an OLE DB source was executing the SQL statement in each OLE DB Command component I defined 21,000 times - once for each row in the table. So instead of executing 4 distinct queries, I was really executing 84,000 queries. If that is the case, when is it OK (if ever) to use such a scenario? Should the SQL command being executed be defined to only work on the current table entry? What would the syntax look like?

My suggestion of moving the data to a table was assuming you were doing a parameter-based update query.

Your understanding is correct. You were executing 84,000 updates, and generally there is never a good time to do that. If you need to perform an update in the data flow on all of those rows, it would be best to insert the changes into a separate table, to be used later in a set-based update.|||Thank you for your latest response and your help with this problem.|||

Jeff-B wrote:

Thank you for your latest response and your help with this problem.

Jeff,

According to your post you managed to achieve this with an Execute SQL Task. Am I correct?

If using an Execute SQL Task is an option for you then I would go with that over a data-flow every time. SSIS will almost never be able to perform quicker than a RDBMS engine.

-Jamie

|||

Jamie,

Yes, I did solve this using an Execute SQL Task. It was a rather straightforward solution with this particular package because I wasn't using a parameterized query. I may have to use what Phil initially suggested above for another, similar package but one that one uses parameters in the query. One parameter needs to be referenced in a sub-query which it isn't allowed. That limitation is what led me to use a data flow task. I just wasn't aware of the inefficiency of that tack. Thanks.

|||

Jeff-B wrote:

Jamie,

Yes, I did solve this using an Execute SQL Task. It was a rather straightforward solution with this particular package because I wasn't using a parameterized query. I may have to use what Phil initially suggested above for another, similar package but one that one uses parameters in the query. One parameter needs to be referenced in a sub-query which it isn't allowed. That limitation is what led me to use a data flow task. I just wasn't aware of the inefficiency of that tack. Thanks.

Caveat that with the fact that its efficient in certain circumstances - unfortunetely doing updates is one of those scenarios. That's due to the vary nature of updates.

-Jamie

sql

Performance of UPDATE commands on individual records

Ok, I'm at a crossroads in my program.
I've got a program that needs to throw an SQL update command to update
some individual records.

>From an efficiency standpoint, does SQL handle the UPDATE command
differently if the field is the same as the old field?
IE
Is it worth doing a string comparision on old vs new data in the
program, or should I just code to update all fields regardless and then
will the server optimize based on whether or not the data actually
changed?
Thanks,
Josh McFarlaneLogically SQL Server doesn't consider the diff at all.
If you want to only update small part of a large volume rows, doing string
comparision mostly yields better performance since that consumes less log
space.
For single line, I still suggest you "update when necessary", since some
triggers may sit there to enforce biz logic.
James
"Josh McFarlane" wrote:

> Ok, I'm at a crossroads in my program.
> I've got a program that needs to throw an SQL update command to update
> some individual records.
>
> differently if the field is the same as the old field?
> IE
> Is it worth doing a string comparision on old vs new data in the
> program, or should I just code to update all fields regardless and then
> will the server optimize based on whether or not the data actually
> changed?
> Thanks,
> Josh McFarlane
>|||Josh McFarlane (darsant@.gmail.com) writes:
> Ok, I'm at a crossroads in my program.
> I've got a program that needs to throw an SQL update command to update
> some individual records.
>
> differently if the field is the same as the old field?
> IE
> Is it worth doing a string comparision on old vs new data in the
> program, or should I just code to update all fields regardless and then
> will the server optimize based on whether or not the data actually
> changed?
Since it's a bit of work, I'm not sure that it's worth the effort, but
there are at least two scenarios where you can gain some performance.
One case is if you use merge replication. I'm not into replication myself,
but I got a question from a guy who is very good at replication, and he
wanted to reduce an update, so that only columns that were actually
changed were to be updated. Apparently, this made replication more
effective.
The other case concerns indexed columns. Consider this:
SELECT * INTO Orders FROM Northwind..Orders
create unique clustered on Orders
create index ix On Orders(CustomerID)
go
BEGIN TRANSACTION
UPDATE Orders
SET EmployeeID = 18
WHERE OrderID = 11000
At this point runs query from another window:
select count(*) from Orders WHERE CustomerID = 'RATTC'
RATTC is the customer id for order 11000. This query returns the
value 18 instantly, was not blocked. Now in the first window do this:
UPDATE Orders
SET EmployeeID = 118,
CustomerID = 'RATTC'
WHERE OrderID = 11000
and now try the SELECT COUNT(*) again. This time it will block.
If you are generatnig the UPDATE statement dynamically, and in client
code, then filtering on columns that have actually changed is probably
manageable. In a stored procedure it is just painful.
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

Performance of dataflow too slow

I was transfering more that 100,000 records from flat file to sql table

It took about 1 hour.Is this the way it is?i used oledb command.

As the data passes by i got to insert to several table.Like i insert some of incoming data to one table then get the key from that table and insert rest of the data with the key field from previous table to another table.

In this case i felt OLedb would be best as we can use query.

I cannot use oledb destination as it has only error output(to insert some of incoming data and i want to have a look up to get the key but oledb des has only error output)

i cannot use sql destination as the database is sql server 2000.It dosent let me.

How can i increase the performance?Please let me know

So if I understand correctly, you are using an OLE DB Command to insert your new records into Table A, then a Lookup in non-cached mode to get the key you just inserted, so you can load the rows (with the key) into Table B with an OLE DB Destination. So you're doing 100K inserts, 100K selects, and then a fast load of 100K rows. I'm not surprised that's slow.

I think you would get much better performance to break this up into two data flows. The first data flow reads the file and populates Table A using an OLE DB Destination in fast load mode. The second Data Flow reads the file again, uses a full-cache lookup from Table A to get your new keys, and then fast loads into Table B.

If you want to get fancy (I like to get fancy), you can read the file only once, assign the Table A key in script, fast load both tables at the same time, and not have to do any lookups.
|||

That's sound advice from Jay. To re-iterate something he said, do what you can to eliminate the OLE DB Commnd. its a very unperformant component.

|||

The best way to improve performance is to get rid of the OLE DB command. If you need to update multiple tables, maybe you should use multiple data flows.

Updated: Jay and Jamie beat me to the punch. You'll notice a common thread in everyone's comments, though...

|||Does it normally take around an hour to insert or update 100,000 records in ssis?|||

sureshv wrote:

Does it normally take around an hour to insert or update 100,000 records in ssis?

If you aren't using the "fast load" option, yes, I can see how that might be the case.|||

sureshv wrote:

Does it normally take around an hour to insert or update 100,000 records in ssis?

Well it really isn't a matter of SSIS performance, the primary factor in your scenario is the relational engine having to process 100,000 commands. Indexes would play a large role in that; if you have too many indexes your inserts will suffer, if you don't have the right one your selects will suffer. Put a WHERE 1=0 at the end of your statements and the performance should improve significantly.

So, yes, it's certainly possible that it could take an hour to insert 100,000 rows. It could also probably be done in 10 minutes with no changes to SSIS. The difference would be in the database itself. But regardless of the database, I think you can improve your SSIS design.

|||

JayH wrote:

Put a WHERE 1=0 at the end of your statements and the performance should improve significantly. .

Really? By not selecting any data?|||

Phil Brammer wrote:

JayH wrote:

Put a WHERE 1=0 at the end of your statements and the performance should improve significantly. .

Really? By not selecting any data?

I said it would be fast, not that it would work.

(It was a joke)
|||

sureshv wrote:

Does it normally take around an hour to insert or update 100,000 records in ssis?

Its impossible to answer this question with a simple yes or no because there are so many variables. Record width, physical location, network latency, CPU, memory, indexes at the destination, insertion method, driver/provider, destination type, .... I could go on and on.

What I CAN tell you is that under the right circumstances it is possible to insert 100000 into a SQL Server table using SSIS withn a few seconds. As I hope you appreciate though, there are many factors.

-Jamie