SQL easy query take illimited time

Question

Post reply

SQL easy query take illimited time

waxime64

SSC Enthusiast

Points: 102
More actions
June 30, 2008 at 2:03 pm

#123594

Hello,
Sorry for my english i speak french
my program, in vb.net 2008 framework 2.0, use SQL server 2000 for retrocompatibility.
but a very easy Update query receive many time a timeout, not always.
"UPDATE tbl_050_CarteVendu SET NombreRestant = NombreRestant - 1 WHERE PkCarteVendu = 10003699"
PkCarteVendu is a bigint, primarykey
NombreRestant is a float
i have 10780 reccord on the table et 95% of the same query are execute in less of 1 sec, but 5 other %, 10003699 in this exemple, receive timeout.
i can reproduce the same error with sql manager with the same query
my first question is if i have indexation error, but no error found ???
please help me

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Have check the system while this query is running to see if there are any blocked processes?

😎

waxime64 SSC Enthusiast Points: 102 More actions · Answer 2

Lynn Pettis (6/30/2008)
Have check the system while this query is running to see if there are any blocked processes?
😎

is create a backup of the database and i restore in a new server, always in sql 2000

same problem

but in the test database, impossible to reproduce

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Have you run a dbcc CheckDB on the database? Read BOL first, as you only want to see if there are any errors, you don't necessarily want to fix them at this point as if there is an error, you need to know what the problem is first.

😎

waxime64 SSC Enthusiast Points: 102 More actions · Answer 4

hi,

i try with dbcc CheckDB and i receive no error

but i reproduce the error

i have one table with primary key in bigInt

in this table i have average 3000 rows (ex: primary key start at 10000)

i add 9000 new rows but with

SET IDENTITY_INSERT tbl_050_CarteVendu ON

and

SET IDENTITY_INSERT tbl_050_CarteVendu OFF

all manual primary key have a lower number of all data in the table (ex: i insert primary key 100 at 9010)

i not sure, but i really think all index is corrupted and for find some value in the table, SQL Serveur read all data without search optimisation

waxime64 SSC Enthusiast Points: 102 More actions · Answer 5

look the problem, is really strange

this query is really slow more of 5 minutes

--------------------------------------

SELECT tblTransactionDetail.PkTransactionDetail, tblTransactionDetail.CodeBarre, tblTransactionDetail.DateEvenement, tblTransactionDetail.Nb_Item,

tblTransactionDetail.MontantCheque, tblTransactionDetail.MontantComptant, tblTransactionDetail.MontantVisa,

tblTransactionDetail.MontantMasterCard, tblTransactionDetail.MontantInterac, tblTransactionDetail.MontantCompteARecevoir,

tblTransactionDetail.MontantAmex, tblTransactionDetail.Mnt_Tax_1_Pour_Rapport, tblTransactionDetail.Mnt_Tax_2_Pour_Rapport,

tblTransactionDetail.Fk_Type_Rabais, tblTransactionDetail.MontantRabais

FROM tbl_050_CarteVendu INNER JOIN

tblTransactionDetail INNER JOIN

tbl_050_JointureCarteDetail ON tblTransactionDetail.CodeBarre = tbl_050_JointureCarteDetail.FK_DetailTransaction ON

tbl_050_CarteVendu.PkCarteVendu = tbl_050_JointureCarteDetail.CodeBarre

WHERE (tblTransactionDetail.Fk_TransactionType_ID = 12) AND (tblTransactionDetail.EvenementHorsCedule = 0) AND

(tblTransactionDetail.EvenementCeduler = 0)

GROUP BY tblTransactionDetail.PkTransactionDetail, tblTransactionDetail.CodeBarre, tblTransactionDetail.DateEvenement, tblTransactionDetail.Nb_Item,

tblTransactionDetail.MontantCheque, tblTransactionDetail.MontantComptant, tblTransactionDetail.MontantVisa,

tblTransactionDetail.MontantMasterCard, tblTransactionDetail.MontantInterac, tblTransactionDetail.MontantCompteARecevoir,

tblTransactionDetail.MontantAmex, tblTransactionDetail.Mnt_Tax_1_Pour_Rapport, tblTransactionDetail.Mnt_Tax_2_Pour_Rapport,

tblTransactionDetail.Fk_Type_Rabais, tblTransactionDetail.MontantRabais

-------------------------------------------

if i remove

-----------

AND (tblTransactionDetail.EvenementHorsCedule = 0) AND

(tblTransactionDetail.EvenementCeduler = 0)

-----------

the query is execute in 00:00:01 seconds

EvenementHorsCedule and EvenementCeduler are a bit ???

please help

they have a real big problem with my BD

-----

Edit

-----

can i optimise my table with change clustered index by NonClustored index and ad included Colomns ??? if yes, how i can change this, with a relationship database without deleting all relationship ???