Showing posts with label guys. Show all posts
Showing posts with label guys. Show all posts

Monday, March 26, 2012

Optimizer Logic Tracing Tool

Guys,
what I need is a tool which gives details on the choice of an
execution plan by the SQL Server. For example, the cost for a hash
join might be 200 and 100 for a nested loop, and therefore a nested
loop is used. Same thing for the access paths for each table/view
involved. In Oracle, we turn on event 100053 to see this kind of info.

Thanx

Daniel"Daniel Roy" <danielroy10junk@.hotmail.com> wrote in message
news:3722db.0401160711.7b1d34b4@.posting.google.com ...
> Guys,
> what I need is a tool which gives details on the choice of an
> execution plan by the SQL Server. For example, the cost for a hash
> join might be 200 and 100 for a nested loop, and therefore a nested
> loop is used. Same thing for the access paths for each table/view
> involved. In Oracle, we turn on event 100053 to see this kind of info.
> Thanx
> Daniel

There's no real equivalent in MSSQL - you can see the plan that optimizer
selected, not the logic it used to arrive at that plan. Query Analyzer can
display the cost of each step in the plan, and how many rows were affected,
either after the query runs or before (by asking for an estimated plan).
This is more or less a graphical presentation of the SET SHOWPLAN_ALL ON
output.

Simon|||> There's no real equivalent in MSSQL - you can see the plan that optimizer
> selected, not the logic it used to arrive at that plan. Query Analyzer can
> display the cost of each step in the plan, and how many rows were affected,
> either after the query runs or before (by asking for an estimated plan).
> This is more or less a graphical presentation of the SET SHOWPLAN_ALL ON
> output.

Isn't there ANY other way to understand the optimizer's logic in
choosing a specific execution plan as opposed to another one?

Daniel

Tuesday, March 20, 2012

optimization challenge

Well i wanted to prove to some guys that cursors are not really that important:shocked: .
:D So this code is suppose to remove duplicate tuples from a table without temporary tables or cursors:D. Except it needs some optimization(and alot of system down time, not sure about that:confused: ).
I would like it, if some one could find an instance of the table when the below code fails or some way to optimize the code or anything;) .

--trashtable for real data
create table abc
(col1 tinyint,
col2 tinyint,
col3 tinyint)

--trash values for trash table
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (3,2,1)
insert into abc values (2,2,3)
insert into abc values (3,2,4)

--check that there are ten rows
select * from abc
--check that there are only five distinct rows
select distinct * from abc

--run code : next 15 line as a batch
declare @.lp tinyint
declare @.col1 tinyint,@.col2 tinyint,@.col3 tinyint
set @.lp=1
while @.lp>0
begin
if not exists (select top 1 * from abc group by col1,col2,col3 having count(col1)>1)
set @.lp=0
else
begin
select top 1 @.col1 = col1,@.col2 = col2,@.col3 = col3 from abc group by col1,col2,col3 having count(col1)>1
delete from abc where col1=@.col1 and col2=@.col2 and col3=@.col3
insert into abc values(@.col1,@.col2,@.col3)
end
end

--only distinct values left in trash table
select * from abc

--think code can be optimized
--just wanted to prove: can be done without cursors or temporary tablesI know this is a cheat and I'm not exactly rising to the challenge however there is a pretty good discussion about (and links to) removing dupes here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256

HTH|||Thanks:cool:

Had a look at the URL.
another cool way is to use SET ROWCOUNT with DELETE.|||While I realize that this uses a temp table, the usage is quite small and this is pretty efficient:
--trashtable for real data
create table abc
( col1 tinyint
, col2 tinyint
, col3 tinyint)

--trash values for trash table
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (3,2,1)
insert into abc values (2,2,3)
insert into abc values (3,2,4)

--check that there are ten rows
select * from abc

--check that there are only five distinct rows
select distinct * from abc

create table ptp_dupes
( col1 tinyint
, col2 tinyint
, col3 tinyint)

INSERT INTO ptp_dupes (col1, col2, col3)
SELECT col1, col2, col3
FROM abc
GROUP BY col1, col2, col3
HAVING 1 < Count(*)

BEGIN TRANSACTION

DELETE FROM abc
WHERE EXISTS (SELECT *
FROM ptp_dupes
WHERE ptp_dupes.col1 = abc.col1
AND ptp_dupes.col2 = abc.col2
AND ptp_dupes.col3 = abc.col3)

INSERT INTO abc (col1, col2, col3)
SELECT col1, col2, col3
FROM ptp_dupes

COMMIT TRANSACTION

SELECT col1, col2, col3 FROM abc ORDER BY col1, col2, col3

SELECT DISTINCT col1, col2, col3 FROM abc ORDER BY col1, col2, col3Unfortunately, there isn't anything I can recommend as even close to efficient that doesn't use a temp table at all. The cursor solutions are inefficient, and the code that you've shown is interesting, but not very efficient.

-PatP

Wednesday, March 7, 2012

Operation cancelled by user

Hello Guys,

I am currently experiencing a Sql error: operation cancelled by user.

I don't know where this error come from. Is it from ADO.NET or SQL Server 2000.

This error doesn't always occur. It only occurs when many users access the web site. I want to know who issue the error. Why issue the error.

Let me know if you have any ideas

Thanks in advanced

See if the following post helps

http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic10278.aspx

|||

Hi Raj Kasi,

Thanks for replying. I did not use sqlcommand.cancel(). Also, the error occurs before I call the finally.

Here is my code:

Dim sqlConnection As New sqlConnection(ConfigurationSettings.AppSettings(String.Format("db.{0}.SQLconnString", Env)))

Dim sbTable As New StringBuilder

Dim strCmpy As String

Dim tblCounters As String

strCmpy = Server.UrlDecode(Request.Cookies("Companies").Value)

Dim sqlCommand As New sqlCommand("sp_getCompanyInfo", sqlConnection)

Dim sqlDataReader As sqlDataReader

Dim myParam As SqlParameter

Dim errLog As New errReport

Try

sqlCommand.CommandType = CommandType.StoredProcedure

sqlConnection.Open()

myParam = sqlCommand.Parameters.Add("@.Companies", SqlDbType.VarChar, 50)

myParam.Value = strCmpy

myParam = sqlCommand.Parameters.Add("@.Class", SqlDbType.VarChar, 2)

myParam.Value = selClass

myParam = sqlCommand.Parameters.Add("@.BrchTmpl", SqlDbType.VarChar, 6)

myParam.Value = selBrchTmpl

sqlDataReader = sqlCommand.ExecuteReader()

Catch ex As Exception

errLog.reportError("Error=" & ex.ToString())

Finally

If Not sqlCommand Is Nothing Then

If Not sqlConnection Is Nothing AndAlso sqlConnection.State <> ConnectionState.Closed Then

sqlCommand.Close()

End If

sqlCommand.Dispose()

End If

If Not sqlConnection Is Nothing Then sqlConnection.Dispose()

End Try

The error occurs when I call sqlCommand.ExecuteReader(). This error only occurs when many users come to use my asp.net application. I use asp.net 1.1 on windows 2000 server framework 1.1 with IIS 6.0.

Thanks

Troy123

|||

Hi Troy123,

Could you see if hitting "stop" or navigating away from the page during operation reproduces this problem? You may need to specify how you treat people navigating away from the page to ensure that it does not kill the back end process explicitly (or maybe you want that to lower the load on your server).

Hope something comes of this,

John (MSFT)

|||

Hi Guys,

I have found the problem. In other pages, I put command.cancel. Now, I removed all command.cancel. It is ok now.

I use ADO.NET 1.1. I think ADO.NET 1.1 doesn't manage command.cancel well.

Regards,

Troy123