how to improve this transaction

Hello,

I have four different transactions such as below and I do one insert and one update in each transaction and it seem it is slow and creates deadlock with the user interface.

These transactions are performed against the tables that users are accessing with another user interface. I have following two questions:

1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did not put index since only two distinct values. Should I put indexes on these fields?

2. Can I make this transaction let user interface do its task in case accessing the same rows, I can start transaction again but I do not want users get disturbed?

.

BEGIN TRANSACTION pTrans

BEGIN

INSERT INTO T1

(fields)

SELECT (fields)

FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID

WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)

UPDATE T2

SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime

FROM T2

WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)

IF @.@.ERROR <> 0

BEGIN

rollback transaction pTrans

return(-1)

END

ELSE

BEGIN

commit transaction pTrans

END

END

What's the schemas of the 3 tables T1,T2 and View1? I tried a test script using your statements, the query went fine and no deadlock occurred.

Q1. Do not put index on low selective column (as you said only 2 distinct values), because it dosen't make sense. Here are some guidelines for choosing columns to index from "Programming a Microsoft? SQL Server? 2000 Database":

Indexes are useful, but they consume disk space and incur overhead and maintenance costs. Consider the following facts and guidelines about indexes:

When you modify data on an indexed column, SQL Server updates the associated indexes.
|||

Thanks for your help, can you let me know how I can make this transaction low isolation level? I am quite new in the transaction isolation level.

|||

Simply use the SET TRANSACTION ISOLATION LEVEL command to set the session option, you can refer to this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp

how to improve this transaction

Hello,

I have four different transactions such as below and I do one insert and one update in each transaction and it seem it is slow and creates deadlock with the user interface.

These transactions are performed against the tables that users are accessing with another user interface. I have following two questions:

1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did not put index since only two distinct values. Should I put indexes on these fields?

2. Can I make this transaction let user interface do its task in case accessing the same rows, I can start transaction again but I do not want users get disturbed?

.

BEGIN TRANSACTION pTrans

BEGIN

INSERT INTO T1

(fields)

SELECT(fields)

FROMT2 INNER JOIN View1 ON T2.TrID = View1.MyTableID

WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)

UPDATET2

SETTextField2 = 'Ok',TextField2Date=@.MyRunDateTime

FROMT2

WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)

IF @.@.ERROR <> 0

BEGIN

rollback transaction pTrans

return(-1)

END

ELSE

BEGIN

commit transaction pTrans

END

END

i think youre just maintaing the data on T2

based from the inserted values on T1.

how about just doing the insert on T1

and placing the update codes on

an "after insert trigger" in t1.

a transaction may not be necessary in this case

the previous design will cause a lot of deadlock in the system

|||

Jim,

You should consider defining indexes on fields listed in "where" clause.
If the problem persists after that, then more detailed examination will be needed.

Indeed, you can invoke asynchronous calls to SQL Server. You can use separate thread to handle the calls to SQL, or you can use built-in support for asynchronous SQL calls. For example, look at SqlCommand.BeginExecuteNonQuery Method at MSDN.

how to improve the speed of sort?

i have found the speed of sort is very slow in my sql (because sql is very complicated, i can't paste it on this page), how can i improve the speed of sort ?

are there better methods?

thks

Look at the execution plan if it shows slow retrieval speed on the ordered columns. If so, you should think about using indexes on the appropiate columns. But you should be able to see this within the execution plan which is a accessible through the Query editor.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||i have saw the execution plan, and i have build index for the table, but i must query result from sub query, the sub query spent most of time. i don't know how to let the sub query more quick?|||The best thing would be *eventually *to use correlated queries, but its hard to tell without knowing the query. Is it not possible to post it in here to help you ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

--Comment: the last aggregate query, it spents 4.44 minutes
--Start for ObjectAgg:LocalNetID,BSCID,SiteID,CellID,StrTime
select
LocalNetID,BSCID,SiteID,CellID,
StrTime,
sum([11601]) as '11601', sum([11602]) as '11602', sum([11603]) as '11603', sum([11604]) as '11604', sum([11605]) as '11605', sum([11606]) as '11606', sum([116060]) as '116060', sum([116061]) as '116061', sum([11607]) as '11607', sum([11608]) as '11608', sum([11609]) as '11609', sum([11610]) as '11610', sum([116100]) as '116100', sum([116101]) as '116101', sum([116102]) as '116102', sum([116103]) as '116103', sum([116104]) as '116104', sum([116105]) as '116105', sum([116106]) as '116106', sum([116107]) as '116107', sum([116108]) as '116108', sum([116109]) as '116109', sum([11611]) as '11611', sum([116110]) as '116110', sum([116111]) as '116111', sum([116112]) as '116112', sum([116113]) as '116113', sum([116114]) as '116114', sum([116115]) as '116115', sum([116116]) as '116116', sum([116117]) as '116117', sum([116118]) as '116118', sum([116119]) as '116119', sum([11612]) as '11612', sum([116120]) as '116120', sum([116121]) as '116121', sum([116122]) as '116122', sum([116123]) as '116123', sum([116124]) as '116124', sum([116125]) as '116125', sum([116126]) as '116126', sum([116127]) as '116127', sum([116128]) as '116128', sum([116129]) as '116129', sum([11613]) as '11613', sum([116130]) as '116130', sum([116131]) as '116131', sum([116132]) as '116132', sum([116133]) as '116133', sum([116134]) as '116134', sum([116135]) as '116135', sum([116136]) as '116136', sum([11614]) as '11614', sum([11615]) as '11615', sum([11616]) as '11616', sum([116160]) as '116160', sum([116161]) as '116161', sum([11617]) as '11617', sum([11618]) as '11618', sum([11619]) as '11619', sum([11620]) as '11620', sum([11621]) as '11621', sum([11622]) as '11622', sum([11623]) as '11623', sum([11624]) as '11624', sum([11625]) as '11625', sum([11626]) as '11626', sum([11627]) as '11627', sum([11628]) as '11628', sum([11629]) as '11629', sum([11630]) as '11630', sum([11631]) as '11631', sum([11632]) as '11632', sum([11633]) as '11633', sum([11634]) as '11634', sum([11635]) as '11635', sum([11636]) as '11636', sum([11637]) as '11637', sum([11638]) as '11638', sum([11639]) as '11639', sum([11640]) as '11640', sum([11641]) as '11641', sum([11642]) as '11642', sum([11643]) as '11643', sum([11644]) as '11644', sum([11645]) as '11645', sum([11646]) as '11646', sum([11647]) as '11647', sum([11648]) as '11648', sum([11649]) as '11649', sum([11650]) as '11650', sum([11651]) as '11651', sum([11652]) as '11652', sum([11653]) as '11653', sum([11654]) as '11654', sum([11655]) as '11655', sum([11656]) as '11656', sum([11657]) as '11657', sum([11658]) as '11658', sum([11659]) as '11659', sum([11660]) as '11660', sum([11661]) as '11661', sum([11662]) as '11662', sum([11663]) as '11663', sum([11664]) as '11664', sum([11665]) as '11665', sum([11666]) as '11666', sum([11667]) as '11667', sum([11668]) as '11668', sum([11669]) as '11669', sum([11670]) as '11670', sum([11671]) as '11671', sum([11672]) as '11672', sum([11673]) as '11673', sum([11674]) as '11674', sum([11675]) as '11675', sum([11676]) as '11676', sum([11677]) as '11677', sum([11678]) as '11678', sum([11679]) as '11679', sum([11680]) as '11680', sum([11681]) as '11681', sum([11682]) as '11682', sum([11683]) as '11683', sum([11684]) as '11684', sum([11685]) as '11685', sum([11686]) as '11686', sum([11687]) as '11687', sum([11688]) as '11688', sum([11689]) as '11689', sum([11690]) as '11690', sum([11691]) as '11691', sum([11692]) as '11692', sum([11693]) as '11693', sum([11694]) as '11694', sum([11695]) as '11695', sum([11696]) as '11696', sum([11697]) as '11697', sum([11698]) as '11698', sum([11699]) as '11699'
from
(
--Comment:the second aggreage query, it spends 2 seconds
--Start for TimeAgg:LocalNetID,BSCID,SiteID,CellID,left(StrTime,12)
select
LocalNetID,BSCID,SiteID,CellID,
left(StrTime,12) as StrTime,
avg([11601] * 1.0) as '11601', avg([11602] * 1.0) as '11602', sum([11603]) as '11603', sum([11604]) as '11604', sum([11605]) as '11605', avg([11606] * 1.0) as '11606', sum([116060]) as '116060', avg([116061] * 1.0) as '116061', avg([11607] * 1.0) as '11607', avg([11608] * 1.0) as '11608', sum([11609]) as '11609', sum([11610]) as '11610', sum([116100]) as '116100', sum([116101]) as '116101', sum([116102]) as '116102', sum([116103]) as '116103', sum([116104]) as '116104', sum([116105]) as '116105', sum([116106]) as '116106', sum([116107]) as '116107', sum([116108]) as '116108', sum([116109]) as '116109', sum([11611]) as '11611', sum([116110]) as '116110', sum([116111]) as '116111', sum([116112]) as '116112', sum([116113]) as '116113', sum([116114]) as '116114', sum([116115]) as '116115', sum([116116]) as '116116', sum([116117]) as '116117', sum([116118]) as '116118', sum([116119]) as '116119', sum([11612]) as '11612', sum([116120]) as '116120', sum([116121]) as '116121', sum([116122]) as '116122', sum([116123]) as '116123', sum([116124]) as '116124', sum([116125]) as '116125', sum([116126]) as '116126', sum([116127]) as '116127', sum([116128]) as '116128', sum([116129]) as '116129', sum([11613]) as '11613', sum([116130]) as '116130', avg([116131] * 1.0) as '116131', sum([116132]) as '116132', sum([116133]) as '116133', sum([116134]) as '116134', sum([116135]) as '116135', sum([116136]) as '116136', sum([11614]) as '11614', sum([11615]) as '11615', avg([11616] * 1.0) as '11616', sum([116160]) as '116160', avg([116161] * 1.0) as '116161', sum([11617]) as '11617', sum([11618]) as '11618', sum([11619]) as '11619', sum([11620]) as '11620', sum([11621]) as '11621', sum([11622]) as '11622', sum([11623]) as '11623', sum([11624]) as '11624', sum([11625]) as '11625', sum([11626]) as '11626', max([11627]) as '11627', avg([11628] * 1.0) as '11628', sum([11629]) as '11629', max([11630]) as '11630', avg([11631] * 1.0) as '11631', sum([11632]) as '11632', avg([11633] * 1.0) as '11633', sum([11634]) as '11634', sum([11635]) as '11635', sum([11636]) as '11636', sum([11637]) as '11637', sum([11638]) as '11638', sum([11639]) as '11639', sum([11640]) as '11640', sum([11641]) as '11641', sum([11642]) as '11642', sum([11643]) as '11643', sum([11644]) as '11644', sum([11645]) as '11645', sum([11646]) as '11646', sum([11647]) as '11647', sum([11648]) as '11648', sum([11649]) as '11649', sum([11650]) as '11650', sum([11651]) as '11651', sum([11652]) as '11652', sum([11653]) as '11653', sum([11654]) as '11654', sum([11655]) as '11655', sum([11656]) as '11656', sum([11657]) as '11657', sum([11658]) as '11658', sum([11659]) as '11659', sum([11660]) as '11660', sum([11661]) as '11661', sum([11662]) as '11662', sum([11663]) as '11663', sum([11664]) as '11664', sum([11665]) as '11665', sum([11666]) as '11666', sum([11667]) as '11667', sum([11668]) as '11668', sum([11669]) as '11669', avg([11670] * 1.0) as '11670', avg([11671] * 1.0) as '11671', avg([11672] * 1.0) as '11672', avg([11673] * 1.0) as '11673', avg([11674] * 1.0) as '11674', sum([11675]) as '11675', sum([11676]) as '11676', sum([11677]) as '11677', sum([11678]) as '11678', sum([11679]) as '11679', sum([11680]) as '11680', avg([11681] * 1.0) as '11681', sum([11682]) as '11682', sum([11683]) as '11683', sum([11684]) as '11684', sum([11685]) as '11685', sum([11686]) as '11686', sum([11687]) as '11687', sum([11688]) as '11688', sum([11689]) as '11689', sum([11690]) as '11690', sum([11691]) as '11691', sum([11692]) as '11692', sum([11693]) as '11693', sum([11694]) as '11694', sum([11695]) as '11695', sum([11696]) as '11696', sum([11697]) as '11697', sum([11698]) as '11698', sum([11699]) as '11699'
from
(
--Comment:first aggregate query, the speed is very quick and it spends no time
--Start for TimeAgg:LocalNetID,BSCID,SiteID,CellID,left(StrTime,14)
select
LocalNetID,BSCID,SiteID,CellID,
left(StrTime,14) as StrTime,
avg([11601] * 1.0) as '11601', avg([11602] * 1.0) as '11602', sum([11603]) as '11603', sum([11604]) as '11604', sum([11605]) as '11605', sum([11606]) as '11606', sum([116060]) as '116060', avg([116061] * 1.0) as '116061', avg([11607] * 1.0) as '11607', avg([11608] * 1.0) as '11608', sum([11609]) as '11609', sum([11610]) as '11610', sum([116100]) as '116100', sum([116101]) as '116101', sum([116102]) as '116102', sum([116103]) as '116103', sum([116104]) as '116104', sum([116105]) as '116105', sum([116106]) as '116106', sum([116107]) as '116107', sum([116108]) as '116108', sum([116109]) as '116109', sum([11611]) as '11611', sum([116110]) as '116110', sum([116111]) as '116111', sum([116112]) as '116112', sum([116113]) as '116113', sum([116114]) as '116114', sum([116115]) as '116115', sum([116116]) as '116116', sum([116117]) as '116117', sum([116118]) as '116118', sum([116119]) as '116119', sum([11612]) as '11612', sum([116120]) as '116120', sum([116121]) as '116121', sum([116122]) as '116122', sum([116123]) as '116123', sum([116124]) as '116124', sum([116125]) as '116125', sum([116126]) as '116126', sum([116127]) as '116127', sum([116128]) as '116128', sum([116129]) as '116129', sum([11613]) as '11613', sum([116130]) as '116130', sum([116131]) as '116131', sum([116132]) as '116132', sum([116133]) as '116133', sum([116134]) as '116134', sum([116135]) as '116135', sum([116136]) as '116136', sum([11614]) as '11614', sum([11615]) as '11615', sum([11616]) as '11616', sum([116160]) as '116160', avg([116161] * 1.0) as '116161', sum([11617]) as '11617', sum([11618]) as '11618', sum([11619]) as '11619', sum([11620]) as '11620', sum([11621]) as '11621', sum([11622]) as '11622', sum([11623]) as '11623', sum([11624]) as '11624', sum([11625]) as '11625', sum([11626]) as '11626', max([11627]) as '11627', avg([11628] * 1.0) as '11628', sum([11629]) as '11629', max([11630]) as '11630', avg([11631] * 1.0) as '11631', sum([11632]) as '11632', avg([11633] * 1.0) as '11633', sum([11634]) as '11634', sum([11635]) as '11635', sum([11636]) as '11636', sum([11637]) as '11637', sum([11638]) as '11638', sum([11639]) as '11639', sum([11640]) as '11640', sum([11641]) as '11641', sum([11642]) as '11642', sum([11643]) as '11643', sum([11644]) as '11644', sum([11645]) as '11645', sum([11646]) as '11646', sum([11647]) as '11647', sum([11648]) as '11648', sum([11649]) as '11649', sum([11650]) as '11650', sum([11651]) as '11651', sum([11652]) as '11652', sum([11653]) as '11653', sum([11654]) as '11654', sum([11655]) as '11655', sum([11656]) as '11656', sum([11657]) as '11657', sum([11658]) as '11658', sum([11659]) as '11659', sum([11660]) as '11660', sum([11661]) as '11661', sum([11662]) as '11662', sum([11663]) as '11663', sum([11664]) as '11664', sum([11665]) as '11665', sum([11666]) as '11666', sum([11667]) as '11667', sum([11668]) as '11668', sum([11669]) as '11669', avg([11670] * 1.0) as '11670', avg([11671] * 1.0) as '11671', avg([11672] * 1.0) as '11672', avg([11673] * 1.0) as '11673', avg([11674] * 1.0) as '11674', sum([11675]) as '11675', sum([11676]) as '11676', sum([11677]) as '11677', sum([11678]) as '11678', sum([11679]) as '11679', sum([11680]) as '11680', avg([11681] * 1.0) as '11681', sum([11682]) as '11682', sum([11683]) as '11683', sum([11684]) as '11684', sum([11685]) as '11685', sum([11686]) as '11686', sum([11687]) as '11687', sum([11688]) as '11688', sum([11689]) as '11689', sum([11690]) as '11690', sum([11691]) as '11691', sum([11692]) as '11692', sum([11693]) as '11693', sum([11694]) as '11694', sum([11695]) as '11695', sum([11696]) as '11696', sum([11697]) as '11697', sum([11698]) as '11698', sum([11699]) as '11699'
from
(
--Start for first join--
select
coalesce(T1.LocalNetID,NULL)as LocalNetID,coalesce(T1.BSCID,NULL)as BSCID,coalesce(T1.SiteID,NULL)as SiteID,coalesce(T1.CellID,NULL)as CellID,
coalesce(T1.StrTime,NULL) as StrTime,
C11601 as '11601',C11602 as '11602',C11603 as '11603',C11604 as '11604',C11605 as '11605',C11606 as '11606',I116061 as '116060',C11606 as '116061',C11607 as '11607',C11608 as '11608',C11609 as '11609',C11610 as '11610',C116100 as '116100',C116101 as '116101',C116102 as '116102',C116103 as '116103',C116104 as '116104',C116105 as '116105',C116106 as '116106',C116107 as '116107',C116108 as '116108',C116109 as '116109',C11611 as '11611',C116110 as '116110',C116111 as '116111',C116112 as '116112',C116113 as '116113',C116114 as '116114',C116115 as '116115',C116116 as '116116',C116117 as '116117',C116118 as '116118',C116119 as '116119',C11612 as '11612',C116120 as '116120',C116121 as '116121',C116122 as '116122',C116123 as '116123',C116124 as '116124',C116125 as '116125',C116126 as '116126',C116127 as '116127',C116128 as '116128',C116129 as '116129',C11613 as '11613',C116130 as '116130',C116131 as '116131',C116132 as '116132',C116133 as '116133',C116134 as '116134',C116135 as '116135',C116136 as '116136',C11614 as '11614',C11615 as '11615',C11616 as '11616',I116161 as '116160',C11616 as '116161',C11617 as '11617',C11618 as '11618',C11619 as '11619',C11620 as '11620',C11621 as '11621',C11622 as '11622',C11623 as '11623',C11624 as '11624',C11625 as '11625',C11626 as '11626',C11627 as '11627',C11628 as '11628',C11629 as '11629',C11630 as '11630',C11631 as '11631',C11632 as '11632',C11633 as '11633',C11634 as '11634',C11635 as '11635',C11636 as '11636',C11637 as '11637',C11638 as '11638',C11639 as '11639',C11640 as '11640',C11641 as '11641',C11642 as '11642',C11643 as '11643',C11644 as '11644',C11645 as '11645',C11646 as '11646',C11647 as '11647',C11648 as '11648',C11649 as '11649',C11650 as '11650',C11651 as '11651',C11652 as '11652',C11653 as '11653',C11654 as '11654',C11655 as '11655',C11656 as '11656',C11657 as '11657',C11658 as '11658',C11659 as '11659',C11660 as '11660',C11661 as '11661',C11662 as '11662',C11663 as '11663',C11664 as '11664',C11665 as '11665',C11666 as '11666',C11667 as '11667',C11668 as '11668',C11669 as '11669',C11670 as '11670',C11671 as '11671',C11672 as '11672',C11673 as '11673',C11674 as '11674',C11675 as '11675',C11676 as '11676',C11677 as '11677',C11678 as '11678',C11679 as '11679',C11680 as '11680',C11681 as '11681',C11682 as '11682',C11683 as '11683',C11684 as '11684',C11685 as '11685',C11686 as '11686',C11687 as '11687',C11688 as '11688',C11689 as '11689',C11690 as '11690',C11691 as '11691',C11692 as '11692',C11693 as '11693',C11694 as '11694',C11695 as '11695',C11696 as '11696',C11697 as '11697',C11698 as '11698',C11699 as '11699'from
--Start for Opt_Cell_pBasicM of inner table--
(
select
LocalNetID,BSCID,SiteID,CellID,
StrTime,RecordDataTime,
C11601,C11602,C11603,C11604,C11605,C11606,I116061,C11607,C11608,C11609,C11610,C116100,C116101,C116102,C116103,C116104,C116105,C116106,C116107,C116108,C116109,C11611,C116110,C116111,C116112,C116113,C116114,C116115,C116116,C116117,C116118,C116119,C11612,C116120,C116121,C116122,C116123,C116124,C116125,C116126,C116127,C116128,C116129,C11613,C116130,C116131,C116132,C116133,C116134,C116135,C116136,C11614,C11615,C11616,I116161,C11617,C11618,C11619,C11620,C11621,C11622,C11623,C11624,C11625,C11626,C11627,C11628,C11629,C11630,C11631,C11632,C11633,C11634,C11635,C11636,C11637,C11638,C11639,C11640,C11641,C11642,C11643,C11644,C11645,C11646,C11647,C11648,C11649,C11650,C11651,C11652,C11653,C11654,C11655,C11656,C11657,C11658,C11659,C11660,C11661,C11662,C11663,C11664,C11665,C11666,C11667,C11668,C11669,C11670,C11671,C11672,C11673,C11674,C11675,C11676,C11677,C11678,C11679,C11680,C11681,C11682,C11683,C11684,C11685,C11686,C11687,C11688,C11689,C11690,C11691,C11692,C11693,C11694,C11695,C11696,C11697,C11698,C11699
from
Opt_Cell_pBasicM
where
((LocalNetID = 1 and BSCID = 5))
and
((RecordDataTime between '2006-11-16 00:01:00' and '2006-11-16 13:00:00'))
and
(GranularityPeriod <= 12)
)
--End for Opt_Cell_pBasicM of inner table--
as T1
--End for first join--
) as T
group by
LocalNetID,BSCID,SiteID,CellID,
left(StrTime,14)
--End for TimeAgg:LocalNetID,BSCID,SiteID,CellID,left(StrTime,14)
) as T
group by
LocalNetID,BSCID,SiteID,CellID,
left(StrTime,12)
--End for TimeAgg:LocalNetID,BSCID,SiteID,CellID,left(StrTime,12)
) as T
group by
LocalNetID,BSCID,SiteID,CellID,
StrTime

--the above sql's execution time is got when there is no data in table Opt_Cell_pBasicM
--because of no data in table, so i am very curious why the last aggregate query need so
--long time, i have saw the execution plan and found the time is spent on sort, but there is
--no data, why to sort?

How to improve the performance from joining to an openquery?

I got the query:

SELECT *

FROM [Test]

left join OpenQuery(ObjName, 'SELECT * FROM A') as A

on ID = A.ID

where B = 21

This query is to slow; even the following query is faster.

SELECT *

FROM OpenQuery(ObjName, 'SELECT * FROM A')

The performance from the following is better:

SELECT A, B, C, (select * from OpenQuery(MD_AS400, 'SELECT * FROM A WHERE ID in (''Val'')')) as D

FROM [Test]

where B = 21

but how can I do make it dinamic? I mean this does not work!

SELECT A, B, C, (select * from OpenQuery(MD_AS400, 'SELECT * FROM A WHERE ID in (''’ + ID + ’'')')) as D

FROM [Test]

where B = 21

Has someone experience with this?

How does this perform?

Code Snippet

SELECT *

FROM [Test]

left join ( SELECT top 100 PERCENT* FROM OpenQuery(ObjName, 'SELECT * FROM A') as subA ) AS A

on ID = A.ID

where B = 21

|||Can you please tell which table the column B belongs? Please use aliases in your query for readability. There are many ways to optimize the query. For example, retrieve only the columns you need from the remote table. Why are you using SELECT * ? Is it necessary. If column B belongs to A then move that inside the pass-through query. Depending on the remote data source (looks like AS400 here) and the driver, SQL Server may not be able to push predicates to the remote server. There are many interfaces in the OLEDB provider for AS400 that needs to be implemented for SQL Server to do this. Also, if B belongs to table A then why do you need to the LEFT JOIN. You should doing inner join which is what will happen. If these suggestions doesn't work then simply dump the openquery results into a temporary table first and then join with the temporary table instead.|||

B belongs to test and I will end up with a teporal table thanks!

SELECT *

FROM [Test]

left join OpenQuery(ObjName, 'SELECT * FROM A') as A

on ID = A.ID

where Test.B = 21

|||That will work but I can not restrict just the first records!

How to improve the efficience of Sql query ?

now i want to get results from server tables, but i found it is very slow, for example :

select Coalesce(T1.Name, T2.Name, T3.Name), T1.M1, T2.M2, T3.M3

from T1

full outer join T2

on Coalesce(T1.Name, NULL) = T2.Name

full outer join T3

on Coalesce(T1.Name, T2.Name) = T3.Name

in Tables i have builded index for name, but when every table have 20000 records, the sql above is very slow, is there other method to improve the query speed ?

Thks

You're trying to full outer join three tables and on the join condition is based on computed columns... well that's a query not build for performance...
If you want to improve performance you need to rewrite the query in a way that doesn't uses coalesce on the join condition, you should use tables columns directly and then filter on the where or on the having

Just a final thought: The
Coalesce(T1.Name, NULL) = T2.Name
Coalesce(T1.Name, T2.Name) = T3.Name
conditions may be rewritten as
T2.Name = Coalesce(T1.Name, NULL)
T3.Name = Coalesce(T1.Name, T2.Name)
take a look if this changes the execution plan.
|||

Liu:

Do you filter based on name or are you wanting to process all rows of all tables?

Dave

|||

every one:

i have solved this problem with hash join such as belows:

select Coalesce(T1.Name, T2.Name, T3.Name), T1.M1, T2.M2, T3.M3

from T1

full outer hash join T2

on Coalesce(T1.Name, NULL) = T2.Name

full outer hash join T3

on Coalesce(T1.Name, T2.Name) = T3.Name

in Tables i have builded index for name, but when every table have 20000 records, the sql above is very slow, is there other method to improve the query speed ?

the time to query changes from 33m to 4s

thks

How to improve performance of Reports?

I HAVE REPORT WITH SEVERAL GROUPS AND FIELDS
ITS RUNNING VERY SLOW
I AM EXTRACTING EVERYTHING FROM 1 TABLE ONLY
HOW CAN I IMPROVE PERFORMANCE OF THE REPORT
CAN ANYONE FROM MICROSOFT LET ME KNOW ALL PERFORMANCE OPTIMIZATION TIPS AND TECHNIQUES FOR THIS TOOLYou'll have to be more specific:
1. How long does it take to get the data from the SELECT statement?
2. How long does it take to export the data as XML (RDL processing, from what I understand)?
3. What is the final output format? We are currently have performance problems with large PDF
reports.
I would love to see some tips for performance as well!
Jami
On Mon, 28 Jun 2004 10:27:01 -0700, Raj <Raj@.discussions.microsoft.com> wrote:
>I HAVE REPORT WITH SEVERAL GROUPS AND FIELDS
>ITS RUNNING VERY SLOW
>I AM EXTRACTING EVERYTHING FROM 1 TABLE ONLY
>HOW CAN I IMPROVE PERFORMANCE OF THE REPORT
>CAN ANYONE FROM MICROSOFT LET ME KNOW ALL PERFORMANCE OPTIMIZATION TIPS AND TECHNIQUES FOR THIS TOOL|||Take a look at this
http://blogs.msdn.com/tudortr/archive/2004/06/28/167969.aspx
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Raj" <Raj@.discussions.microsoft.com> wrote in message
news:F3580BB2-C509-40F3-BD9D-062DC03A87A0@.microsoft.com...
> I HAVE REPORT WITH SEVERAL GROUPS AND FIELDS
> ITS RUNNING VERY SLOW
> I AM EXTRACTING EVERYTHING FROM 1 TABLE ONLY
> HOW CAN I IMPROVE PERFORMANCE OF THE REPORT
> CAN ANYONE FROM MICROSOFT LET ME KNOW ALL PERFORMANCE OPTIMIZATION TIPS
AND TECHNIQUES FOR THIS TOOL|||We definitely need more information. This should not be a problem at all (at
least from a complexity viewpoint).
Bruce L-C
"Raj" <Raj@.discussions.microsoft.com> wrote in message
news:F3580BB2-C509-40F3-BD9D-062DC03A87A0@.microsoft.com...
> I HAVE REPORT WITH SEVERAL GROUPS AND FIELDS
> ITS RUNNING VERY SLOW
> I AM EXTRACTING EVERYTHING FROM 1 TABLE ONLY
> HOW CAN I IMPROVE PERFORMANCE OF THE REPORT
> CAN ANYONE FROM MICROSOFT LET ME KNOW ALL PERFORMANCE OPTIMIZATION TIPS
AND TECHNIQUES FOR THIS TOOL