mermood
asked on
Sybase Isolation Level 0
I am trying to run a query on Sybase using isolation level 0 in the hope that it would be faster. However, it's actually much slower than running it on isolation 1.
It says in the Sybase manual that an identity column is needed for tables with non unique index. I created an identity column but it doesn't seem to help.
I would appreciate any help.
It says in the Sybase manual that an identity column is needed for tables with non unique index. I created an identity column but it doesn't seem to help.
I would appreciate any help.
ASKER
DDL
=====
CREATE TABLE dbo.winTrade
(
id varchar(32) NOT NULL,
source varchar(10) NOT NULL,
tradeXml text NOT NULL,
isSynthentic tinyint NOT NULL,
isActive tinyint NOT NULL,
lastUpdated datetime NOT NULL,
CONSTRAINT winTrade_5920021091
PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
ON dbo.winTrade(source,isSynt
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade
PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go
Query
======
select
trade0_.id as id,
trade0_.source as source,
trade0_.tradeXml as tradeXml,
trade0_.isSynthentic as isSynthe4_,
trade0_.isActive as isActive,
trade0_.lastUpdated as lastUpda6_
from winTrade trade0_
where trade0_.source ='SALES'
and trade0_.isSynthentic =0
and trade0_.isActive =1
and (not exists (select tradematch1_.tradeId1
from winTradeMatch_temp tradematch1_,
winRuleContext rulecontex2_
where tradematch1_.contextId = rulecontex2_.id
and rulecontex2_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch1_.tradeId1))
and (not exists (select tradematch3_.tradeId2
from winTradeMatch_temp tradematch3_,
winRuleContext rulecontex4_
where tradematch3_.contextId = rulecontex4_.id
and rulecontex4_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch3_.tradeId2))
=====
CREATE TABLE dbo.winTrade
(
id varchar(32) NOT NULL,
source varchar(10) NOT NULL,
tradeXml text NOT NULL,
isSynthentic tinyint NOT NULL,
isActive tinyint NOT NULL,
lastUpdated datetime NOT NULL,
CONSTRAINT winTrade_5920021091
PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
ON dbo.winTrade(source,isSynt
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade
PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go
Query
======
select
trade0_.id as id,
trade0_.source as source,
trade0_.tradeXml as tradeXml,
trade0_.isSynthentic as isSynthe4_,
trade0_.isActive as isActive,
trade0_.lastUpdated as lastUpda6_
from winTrade trade0_
where trade0_.source ='SALES'
and trade0_.isSynthentic =0
and trade0_.isActive =1
and (not exists (select tradematch1_.tradeId1
from winTradeMatch_temp tradematch1_,
winRuleContext rulecontex2_
where tradematch1_.contextId = rulecontex2_.id
and rulecontex2_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch1_.tradeId1))
and (not exists (select tradematch3_.tradeId2
from winTradeMatch_temp tradematch3_,
winRuleContext rulecontex4_
where tradematch3_.contextId = rulecontex4_.id
and rulecontex4_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch3_.tradeId2))
ASKER
DDL
=====
CREATE TABLE dbo.winTrade
(
id varchar(32) NOT NULL,
source varchar(10) NOT NULL,
tradeXml text NOT NULL,
isSynthentic tinyint NOT NULL,
isActive tinyint NOT NULL,
lastUpdated datetime NOT NULL,
CONSTRAINT winTrade_5920021091
PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
ON dbo.winTrade(source,isSynt
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade
PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go
Query
======
select
trade0_.id as id,
trade0_.source as source,
trade0_.tradeXml as tradeXml,
trade0_.isSynthentic as isSynthe4_,
trade0_.isActive as isActive,
trade0_.lastUpdated as lastUpda6_
from winTrade trade0_
where trade0_.source ='SALES'
and trade0_.isSynthentic =0
and trade0_.isActive =1
and (not exists (select tradematch1_.tradeId1
from winTradeMatch_temp tradematch1_,
winRuleContext rulecontex2_
where tradematch1_.contextId = rulecontex2_.id
and rulecontex2_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch1_.tradeId1))
and (not exists (select tradematch3_.tradeId2
from winTradeMatch_temp tradematch3_,
winRuleContext rulecontex4_
where tradematch3_.contextId = rulecontex4_.id
and rulecontex4_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch3_.tradeId2))
=====
CREATE TABLE dbo.winTrade
(
id varchar(32) NOT NULL,
source varchar(10) NOT NULL,
tradeXml text NOT NULL,
isSynthentic tinyint NOT NULL,
isActive tinyint NOT NULL,
lastUpdated datetime NOT NULL,
CONSTRAINT winTrade_5920021091
PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
ON dbo.winTrade(source,isSynt
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade
PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go
Query
======
select
trade0_.id as id,
trade0_.source as source,
trade0_.tradeXml as tradeXml,
trade0_.isSynthentic as isSynthe4_,
trade0_.isActive as isActive,
trade0_.lastUpdated as lastUpda6_
from winTrade trade0_
where trade0_.source ='SALES'
and trade0_.isSynthentic =0
and trade0_.isActive =1
and (not exists (select tradematch1_.tradeId1
from winTradeMatch_temp tradematch1_,
winRuleContext rulecontex2_
where tradematch1_.contextId = rulecontex2_.id
and rulecontex2_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch1_.tradeId1))
and (not exists (select tradematch3_.tradeId2
from winTradeMatch_temp tradematch3_,
winRuleContext rulecontex4_
where tradematch3_.contextId = rulecontex4_.id
and rulecontex4_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch3_.tradeId2))
ASKER
DDL
=====
CREATE TABLE dbo.winTrade
(
id varchar(32) NOT NULL,
source varchar(10) NOT NULL,
tradeXml text NOT NULL,
isSynthentic tinyint NOT NULL,
isActive tinyint NOT NULL,
lastUpdated datetime NOT NULL,
CONSTRAINT winTrade_5920021091
PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
ON dbo.winTrade(source,isSynt
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade
PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go
Query
======
select
trade0_.id as id,
trade0_.source as source,
trade0_.tradeXml as tradeXml,
trade0_.isSynthentic as isSynthe4_,
trade0_.isActive as isActive,
trade0_.lastUpdated as lastUpda6_
from winTrade trade0_
where trade0_.source ='SALES'
and trade0_.isSynthentic =0
and trade0_.isActive =1
and (not exists (select tradematch1_.tradeId1
from winTradeMatch_temp tradematch1_,
winRuleContext rulecontex2_
where tradematch1_.contextId = rulecontex2_.id
and rulecontex2_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch1_.tradeId1))
and (not exists (select tradematch3_.tradeId2
from winTradeMatch_temp tradematch3_,
winRuleContext rulecontex4_
where tradematch3_.contextId = rulecontex4_.id
and rulecontex4_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch3_.tradeId2))
=====
CREATE TABLE dbo.winTrade
(
id varchar(32) NOT NULL,
source varchar(10) NOT NULL,
tradeXml text NOT NULL,
isSynthentic tinyint NOT NULL,
isActive tinyint NOT NULL,
lastUpdated datetime NOT NULL,
CONSTRAINT winTrade_5920021091
PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
ON dbo.winTrade(source,isSynt
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade
PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go
Query
======
select
trade0_.id as id,
trade0_.source as source,
trade0_.tradeXml as tradeXml,
trade0_.isSynthentic as isSynthe4_,
trade0_.isActive as isActive,
trade0_.lastUpdated as lastUpda6_
from winTrade trade0_
where trade0_.source ='SALES'
and trade0_.isSynthentic =0
and trade0_.isActive =1
and (not exists (select tradematch1_.tradeId1
from winTradeMatch_temp tradematch1_,
winRuleContext rulecontex2_
where tradematch1_.contextId = rulecontex2_.id
and rulecontex2_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch1_.tradeId1))
and (not exists (select tradematch3_.tradeId2
from winTradeMatch_temp tradematch3_,
winRuleContext rulecontex4_
where tradematch3_.contextId = rulecontex4_.id
and rulecontex4_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch3_.tradeId2))
ASKER
DDL
=====
CREATE TABLE dbo.winTrade
(
id varchar(32) NOT NULL,
source varchar(10) NOT NULL,
tradeXml text NOT NULL,
isSynthentic tinyint NOT NULL,
isActive tinyint NOT NULL,
lastUpdated datetime NOT NULL,
CONSTRAINT winTrade_5920021091
PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
ON dbo.winTrade(source,isSynt
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade
PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go
Query
======
select
trade0_.id as id,
trade0_.source as source,
trade0_.tradeXml as tradeXml,
trade0_.isSynthentic as isSynthe4_,
trade0_.isActive as isActive,
trade0_.lastUpdated as lastUpda6_
from winTrade trade0_
where trade0_.source ='SALES'
and trade0_.isSynthentic =0
and trade0_.isActive =1
and (not exists (select tradematch1_.tradeId1
from winTradeMatch_temp tradematch1_,
winRuleContext rulecontex2_
where tradematch1_.contextId = rulecontex2_.id
and rulecontex2_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch1_.tradeId1))
and (not exists (select tradematch3_.tradeId2
from winTradeMatch_temp tradematch3_,
winRuleContext rulecontex4_
where tradematch3_.contextId = rulecontex4_.id
and rulecontex4_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch3_.tradeId2))
=====
CREATE TABLE dbo.winTrade
(
id varchar(32) NOT NULL,
source varchar(10) NOT NULL,
tradeXml text NOT NULL,
isSynthentic tinyint NOT NULL,
isActive tinyint NOT NULL,
lastUpdated datetime NOT NULL,
CONSTRAINT winTrade_5920021091
PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
ON dbo.winTrade(source,isSynt
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade
PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go
Query
======
select
trade0_.id as id,
trade0_.source as source,
trade0_.tradeXml as tradeXml,
trade0_.isSynthentic as isSynthe4_,
trade0_.isActive as isActive,
trade0_.lastUpdated as lastUpda6_
from winTrade trade0_
where trade0_.source ='SALES'
and trade0_.isSynthentic =0
and trade0_.isActive =1
and (not exists (select tradematch1_.tradeId1
from winTradeMatch_temp tradematch1_,
winRuleContext rulecontex2_
where tradematch1_.contextId = rulecontex2_.id
and rulecontex2_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch1_.tradeId1))
and (not exists (select tradematch3_.tradeId2
from winTradeMatch_temp tradematch3_,
winRuleContext rulecontex4_
where tradematch3_.contextId = rulecontex4_.id
and rulecontex4_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch3_.tradeId2))
ASKER
DDL
=====
CREATE TABLE dbo.winTrade
(
id varchar(32) NOT NULL,
source varchar(10) NOT NULL,
tradeXml text NOT NULL,
isSynthentic tinyint NOT NULL,
isActive tinyint NOT NULL,
lastUpdated datetime NOT NULL,
CONSTRAINT winTrade_5920021091
PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
ON dbo.winTrade(source,isSynt
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade
PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go
Query
======
select
trade0_.id as id,
trade0_.source as source,
trade0_.tradeXml as tradeXml,
trade0_.isSynthentic as isSynthe4_,
trade0_.isActive as isActive,
trade0_.lastUpdated as lastUpda6_
from winTrade trade0_
where trade0_.source ='SALES'
and trade0_.isSynthentic =0
and trade0_.isActive =1
and (not exists (select tradematch1_.tradeId1
from winTradeMatch_temp tradematch1_,
winRuleContext rulecontex2_
where tradematch1_.contextId = rulecontex2_.id
and rulecontex2_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch1_.tradeId1))
and (not exists (select tradematch3_.tradeId2
from winTradeMatch_temp tradematch3_,
winRuleContext rulecontex4_
where tradematch3_.contextId = rulecontex4_.id
and rulecontex4_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch3_.tradeId2))
=====
CREATE TABLE dbo.winTrade
(
id varchar(32) NOT NULL,
source varchar(10) NOT NULL,
tradeXml text NOT NULL,
isSynthentic tinyint NOT NULL,
isActive tinyint NOT NULL,
lastUpdated datetime NOT NULL,
CONSTRAINT winTrade_5920021091
PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
ON dbo.winTrade(source,isSynt
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade
PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go
Query
======
select
trade0_.id as id,
trade0_.source as source,
trade0_.tradeXml as tradeXml,
trade0_.isSynthentic as isSynthe4_,
trade0_.isActive as isActive,
trade0_.lastUpdated as lastUpda6_
from winTrade trade0_
where trade0_.source ='SALES'
and trade0_.isSynthentic =0
and trade0_.isActive =1
and (not exists (select tradematch1_.tradeId1
from winTradeMatch_temp tradematch1_,
winRuleContext rulecontex2_
where tradematch1_.contextId = rulecontex2_.id
and rulecontex2_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch1_.tradeId1))
and (not exists (select tradematch3_.tradeId2
from winTradeMatch_temp tradematch3_,
winRuleContext rulecontex4_
where tradematch3_.contextId = rulecontex4_.id
and rulecontex4_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch3_.tradeId2))
ASKER
DDL
=====
CREATE TABLE dbo.winTrade
(
id varchar(32) NOT NULL,
source varchar(10) NOT NULL,
tradeXml text NOT NULL,
isSynthentic tinyint NOT NULL,
isActive tinyint NOT NULL,
lastUpdated datetime NOT NULL,
CONSTRAINT winTrade_5920021091
PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
ON dbo.winTrade(source,isSynt
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade
PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go
Query
======
select
trade0_.id as id,
trade0_.source as source,
trade0_.tradeXml as tradeXml,
trade0_.isSynthentic as isSynthe4_,
trade0_.isActive as isActive,
trade0_.lastUpdated as lastUpda6_
from winTrade trade0_
where trade0_.source ='SALES'
and trade0_.isSynthentic =0
and trade0_.isActive =1
and (not exists (select tradematch1_.tradeId1
from winTradeMatch_temp tradematch1_,
winRuleContext rulecontex2_
where tradematch1_.contextId = rulecontex2_.id
and rulecontex2_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch1_.tradeId1))
and (not exists (select tradematch3_.tradeId2
from winTradeMatch_temp tradematch3_,
winRuleContext rulecontex4_
where tradematch3_.contextId = rulecontex4_.id
and rulecontex4_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch3_.tradeId2))
=====
CREATE TABLE dbo.winTrade
(
id varchar(32) NOT NULL,
source varchar(10) NOT NULL,
tradeXml text NOT NULL,
isSynthentic tinyint NOT NULL,
isActive tinyint NOT NULL,
lastUpdated datetime NOT NULL,
CONSTRAINT winTrade_5920021091
PRIMARY KEY CLUSTERED (id)
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.winTrade') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.winTrade >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.winTrade >>>'
go
CREATE INDEX winTrade1
ON dbo.winTrade(source,isSynt
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.winTrade
PRINT '<<< CREATED INDEX dbo.winTrade.winTrade1 >>>'
ELSE
PRINT '<<< FAILED CREATING INDEX dbo.winTrade.winTrade1 >>>'
go
Query
======
select
trade0_.id as id,
trade0_.source as source,
trade0_.tradeXml as tradeXml,
trade0_.isSynthentic as isSynthe4_,
trade0_.isActive as isActive,
trade0_.lastUpdated as lastUpda6_
from winTrade trade0_
where trade0_.source ='SALES'
and trade0_.isSynthentic =0
and trade0_.isActive =1
and (not exists (select tradematch1_.tradeId1
from winTradeMatch_temp tradematch1_,
winRuleContext rulecontex2_
where tradematch1_.contextId = rulecontex2_.id
and rulecontex2_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch1_.tradeId1))
and (not exists (select tradematch3_.tradeId2
from winTradeMatch_temp tradematch3_,
winRuleContext rulecontex4_
where tradematch3_.contextId = rulecontex4_.id
and rulecontex4_.typ ='TRADE_MATCHING'
and trade0_.id = tradematch3_.tradeId2))
ASKER
ooops not quite sure what happened with my browser there.
There are 2 more tables involved - winTradeMatch_temp and winRuleContext .
There are about 1000 rows in winTrade.
There are 2 more tables involved - winTradeMatch_temp and winRuleContext .
There are about 1000 rows in winTrade.
Isolation level 0 is about being more relaxed with locking - so it is an optimisation for concurrency/throughput, not for response time. In fact you can generally be sure that anything that improves concurrency makes response time worse!
If you were running this on its own in a server where no-one else was running anything, isolation level 0 *at best* will run the same as it did without it. It will never run faster.
Isolation level 0 says to do dirty reads, which means if someone else is writing to one or more of the underlying tables and has not yet committed their transaction, rather than being blocked by that transaction (default behaviour, or isolation level 1), we will be allowed to read the uncommitted data.
Apart from anything else this means we don't know what results the query will return, since we don't know if we'll be reading the data before they write to it, after they've written to it, or maybe even halfway through whatever inserts/updates/deletes they're doing. In other words this is no longer a trusted result. Dirty reads are only for when you can tolerate a "ballpark" figure rather than exactly correct results.
So let's say that's ok, we understand we have untrusted results that might be wrong. We don't want to be blocked by any writes people are doing on the table(s)... except that the table is already row-level locked, so unless people are constantly writing to the table (and taking a long time to do it), we're probably not going to be blocked very much anyway... unless they're taking out exclusive table locks, in which case datarows locking won't help you.
If that's the case, there are almost certainly better ways to fix the problem of blocking locks - we'd ask why there are EX TBL locks and have a look at fixing those.
I note also that datarows locking is another concurrency optimisation... so in fact you made your response time worse by using row-level locking.
Lastly, isolation level 0 needs a unique index, so if a row changes while we're reading the table we know if we've seen it before or not. That business about identity columns is because the ASE engine can "cheat" and use the identity as part of a unique index.
The problem is that we *have* to use a unique index (or implicit one via the identity column) for every access to the table... whether or not that is actually a good way of finding our rows or not. So effectively you've told the query optimiser it *must* use the unique index (or fake one using the identity column) for every access to the table, regardless of what it wants to use. This is almost certainly why things got slower.
I would suggest that rather than being distracted by the shiny technology, you take a step back and look at exactly what the problem is. Why did you want to use isolation level 0? Why did you want to use datarows locking? Are you having lots of locking or deadlocking problems? If your only motivations for these were to "make it go faster" then I'm afraid you've made things worse not better.
If you were running this on its own in a server where no-one else was running anything, isolation level 0 *at best* will run the same as it did without it. It will never run faster.
Isolation level 0 says to do dirty reads, which means if someone else is writing to one or more of the underlying tables and has not yet committed their transaction, rather than being blocked by that transaction (default behaviour, or isolation level 1), we will be allowed to read the uncommitted data.
Apart from anything else this means we don't know what results the query will return, since we don't know if we'll be reading the data before they write to it, after they've written to it, or maybe even halfway through whatever inserts/updates/deletes they're doing. In other words this is no longer a trusted result. Dirty reads are only for when you can tolerate a "ballpark" figure rather than exactly correct results.
So let's say that's ok, we understand we have untrusted results that might be wrong. We don't want to be blocked by any writes people are doing on the table(s)... except that the table is already row-level locked, so unless people are constantly writing to the table (and taking a long time to do it), we're probably not going to be blocked very much anyway... unless they're taking out exclusive table locks, in which case datarows locking won't help you.
If that's the case, there are almost certainly better ways to fix the problem of blocking locks - we'd ask why there are EX TBL locks and have a look at fixing those.
I note also that datarows locking is another concurrency optimisation... so in fact you made your response time worse by using row-level locking.
Lastly, isolation level 0 needs a unique index, so if a row changes while we're reading the table we know if we've seen it before or not. That business about identity columns is because the ASE engine can "cheat" and use the identity as part of a unique index.
The problem is that we *have* to use a unique index (or implicit one via the identity column) for every access to the table... whether or not that is actually a good way of finding our rows or not. So effectively you've told the query optimiser it *must* use the unique index (or fake one using the identity column) for every access to the table, regardless of what it wants to use. This is almost certainly why things got slower.
I would suggest that rather than being distracted by the shiny technology, you take a step back and look at exactly what the problem is. Why did you want to use isolation level 0? Why did you want to use datarows locking? Are you having lots of locking or deadlocking problems? If your only motivations for these were to "make it go faster" then I'm afraid you've made things worse not better.
ASKER
The problem is that I am having a number of locking and deadlocking issues. Hence, I wanted to use isolation level 0 or dirty reads. The deadlock occurs when one thread on winTrade table and trying to get a lock on winTradeMatchTemp and another thread is writing to winTradeMatchTemp and trying to get a lock on winTrade.
I have changed the definitions on winTradeMatchTemp and winTrade from APL to DOL and that seems to do the trick. It is not running any faster but at least not any slower.
Any concerns about APL -> DOL?
I have changed the definitions on winTradeMatchTemp and winTrade from APL to DOL and that seems to do the trick. It is not running any faster but at least not any slower.
Any concerns about APL -> DOL?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I agree with Joe about a lot of things and datapages versus datarows locking is one of them. There are certainly circumstances where you need datarows locking however, I have found them to be just a few select cases, e.g. workflow applications, queues in tables, etc. All of this assumes the application is designed/coded reasonably well.
Bill
Bill
ASKER
You brought up a very good point - datapages. I will try datapages and see what the performance is.
Actually, what's the difference between datarows and datapages. I have been told datarows is more efficient?
Actually, what's the difference between datarows and datapages. I have been told datarows is more efficient?
ASKER
Or rather when do you use datarows vs datapages?
Allpages - lock one page at a time. Lock any index pages you touched as part of touching the data page(s). (So if doing a SELECT through an index, lock the index pages too; a SELECT that table scans won't lock the index pages.)
DOL - data only locking, never lock index pages
Datapages - like allpages, but only the data pages. Indexes are never locks. Any access of a data page locks the entire page.
Datarows - row level locking. Never lock indexes.
DOL in general have more overhead for space, response times and ongoing maintainenance. Datarows is more expensive than datapages for two main reasons:
1) You need more locks! If you can fit 20 rows on a page, then accessing the same data now needs 20 times as many locks. They eat memory which you'd really rather spend on caches instead.
2) More overhead! ASE has to track lock dependencies, lock grants, lock waits, deadlocks, etc, at a finer level of granularity. It's like standing by a busy railway and trying to count individual people in the carriages vs just counting the trains.
I think best practice to reduce locking problems and deadlocks is:
1) Fix all SQL and transaction design issues in the code (this gains you 80% of the benefits)
2) Switch one table at a time to datapages locking. Do not switch all of them at once! (15%)
3) If all tables involved are now datapages, switch one of them at a time - starting with the smallest! - to datarows. (5%)
You will occasionally see some written recommendations to go directly to datarows and only fall back to datapages if something goes wrong. I'm really not sure where that comes from, I think there was early distrust of datapages as a "compromise" solution when DOL was introduced in ASE 11.9.2. Yet when most locking problems are on index pages rather than data pages (stands to reason - you might fit 20 rows on a data page, but 200 on an index page), then datapages is a good first choice.
DOL - data only locking, never lock index pages
Datapages - like allpages, but only the data pages. Indexes are never locks. Any access of a data page locks the entire page.
Datarows - row level locking. Never lock indexes.
DOL in general have more overhead for space, response times and ongoing maintainenance. Datarows is more expensive than datapages for two main reasons:
1) You need more locks! If you can fit 20 rows on a page, then accessing the same data now needs 20 times as many locks. They eat memory which you'd really rather spend on caches instead.
2) More overhead! ASE has to track lock dependencies, lock grants, lock waits, deadlocks, etc, at a finer level of granularity. It's like standing by a busy railway and trying to count individual people in the carriages vs just counting the trains.
I think best practice to reduce locking problems and deadlocks is:
1) Fix all SQL and transaction design issues in the code (this gains you 80% of the benefits)
2) Switch one table at a time to datapages locking. Do not switch all of them at once! (15%)
3) If all tables involved are now datapages, switch one of them at a time - starting with the smallest! - to datarows. (5%)
You will occasionally see some written recommendations to go directly to datarows and only fall back to datapages if something goes wrong. I'm really not sure where that comes from, I think there was early distrust of datapages as a "compromise" solution when DOL was introduced in ASE 11.9.2. Yet when most locking problems are on index pages rather than data pages (stands to reason - you might fit 20 rows on a data page, but 200 on an index page), then datapages is a good first choice.
ASKER
I have just switch the tables from datarows to datapages. And I am seeing the same performance on them! I might just use datapages for the larger table (ie. 30 000 rows) and datarows for the smaller one (ie. 3 rows).
Thanks for your help!!!
Thanks for your help!!!
I won't say there's no good reason to ever use datarows... just no good reason to go to datarows without trying datapages first. 8-)
- what does your table look like (DDL)
- what does it contain that it does not have a unique business key
- what is your query (DML)
- how much data is in the table
- what are your keys and indexes (more DDL)
- are there any other tables involved in the query
With that we can start to figure out what your needs are. There are some legitimate cases when you don't have a unique business key on a row (register tape on a cash register is an example) so you may be jumping through hoops you don't have to.
Also, the effect of an identity column will be felt at INSERT time when the value is assigned to the column, not at query time.
For most queries, the isolation level will have an immeasurably small affect on performance. I suspect there are multiple thing going on here.
Regards,
Bill