Sql Select query is not working on one table but the same is working on copy of the table
-
Sql Select query is not working on one table but the same is working on copy of the table - I have a table in my database when I execute a select query (joining the same table to apply different conditions), not giving the any results (continuously executing, never ending).
I have created a new table with same structure and loaded all the data into the temporary created newly. The same select query giving results.
I deleted the data from the main table and loaded back from the temporary table, now the same select query giving results from main table also.
I didn't understand the problem, looking like not a data problem and not a table problem. It may be because of some default settings or status of any data.
Could you please help me, what could be the reason for the problem.
Thanks,
PRaveen
-
Could the table have been locked by another process?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Thank you very much Phil for the reply...
But it is happening every time, tested in different database copies.
-
What is the query waiting for?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Actually joining the same table n number of times based on the where clause user selected from UI... for each where joining once the able.
select * from (select distinct T65.[Sequenom_ID],T65.[Lab_Number],T65.[BlockID],T65.[Mutation],T65.[Status],T65.[ChipID],T50.FName + ' '+ T50.LName as [Created By],T65.[Created_on],T65.Seq_Status,Block_Type as [Block Type],ClinicalTrials as [Clinical Trial],case when T1.Patient_Hold_ID=1 then 'Patient Death' when NGame_Hold=1 then 'Ngame Hold' when [dbo].[getTrials_TrialsOnHold_Sequenom].ClinicalTrialsOnHold <> '' then 'Clinical Trial Hold' else '' end as [Hold],T1.PatientID,T1.InstituteNumber,T1.Amendment3,[dbo].[getTrials_TrialsOnHold_Sequenom].ClinicalTrialsOnHold as [TrialsOnHold],dnayield FROM [T65_Sequenom_Results] T65 join T50_User_Main T50 on (T65.T50_Created_By=T50.User_ID and T65.Record_Status = 'Active') join T3_Form_BLK T3 on (T65.Lab_Number=T3.Lab_ID and T3.Record_Status = 'Active' and Resubmission_No=0)
join T1_Patient_Details T1 on (T1.PatientID=T3.T1_PatientID and T1.Record_Status = 'Active')
join T56_Sites T56 on (T56.SiteID=T1.InstituteNumber and T56.Record_Status = 'Active') cross apply [dbo].[getTrials_TrialsOnHold_Sequenom](T65.[Lab_Number],T65.[BlockID],T1.InstituteNumber)
Join (select [Lab_Number],[BlockID] from [T65_Sequenom_Results] where Record_Status='Active' and mutation='BRAFmutated' and status='0') T20 on (T20.[Lab_Number] = T65.[Lab_Number] and T20.[BlockID]=T65.[BlockID])
Join (select [Lab_Number],[BlockID] from [T65_Sequenom_Results] where Record_Status='Active' and mutation='KRASmutated' and status='0') T21 on (T21.[Lab_Number] = T65.[Lab_Number] and T21.[BlockID]=T65.[BlockID])
Join (select [Lab_Number],[BlockID] from [T65_Sequenom_Results] where Record_Status='Active' and mutation='NRASmutated' and status='0') T22 on (T22.[Lab_Number] = T65.[Lab_Number] and T22.[BlockID]=T65.[BlockID])
Join (select [Lab_Number],[BlockID] from [T65_Sequenom_Results] where Record_Status='Active' and mutation='PIK3CAmutated' and status='0') T23 on (T23.[Lab_Number] = T65.[Lab_Number] and T23.[BlockID]=T65.[BlockID]) ) T order by T.[Lab_Number] desc
-
-
You've probably got an optimiser timeout on that query. I count ten tables in the FROM list, and that's counting the table-valued function as one table. More than about seven tables and you risk a timeout. Whenever the query recompiles, such as when you point it at different tables, SQL Server can't get an optimum plan within the allotted time so spits out a plan which is guaranteed to generate the correct results regardless of how long it takes to run - and each time it recompiles, you'll get a different suboptimal plan. Sometimes you will get lucky and the plan will run quickly.
This query is horribly inefficient, table T65_Sequenom_Results is read at least five times. Here it is with a little formatting:
select *from (
select distinct
T65.[Sequenom_ID],
T65.[Lab_Number],
T65.[BlockID],
T65.[Mutation],
T65.[Status],
T65.[ChipID],
T50.FName + ' '+ T50.LName as [Created By],
T65.[Created_on],
T65.Seq_Status,
Block_Type as [Block Type],
ClinicalTrials as [Clinical Trial],
case
when T1.Patient_Hold_ID=1 then 'Patient Death'
when NGame_Hold=1 then 'Ngame Hold'
when [dbo].[getTrials_TrialsOnHold_Sequenom].ClinicalTrialsOnHold <> '' then 'Clinical Trial Hold'
else '' end as [Hold],T1.PatientID,
T1.InstituteNumber,
T1.Amendment3,
[dbo].[getTrials_TrialsOnHold_Sequenom].ClinicalTrialsOnHold as [TrialsOnHold],
dnayield
FROM [T65_Sequenom_Results] T65
join T50_User_Main T50
on (T65.T50_Created_By=T50.User_ID and T65.Record_Status = 'Active')
join T3_Form_BLK T3
on (T65.Lab_Number=T3.Lab_ID and T3.Record_Status = 'Active' and Resubmission_No=0)
join T1_Patient_Details T1
on (T1.PatientID=T3.T1_PatientID and T1.Record_Status = 'Active')
join T56_Sites T56
on (T56.SiteID=T1.InstituteNumber and T56.Record_Status = 'Active')
cross apply [dbo].[getTrials_TrialsOnHold_Sequenom](T65.[Lab_Number],T65.[BlockID],T1.InstituteNumber)
Join (
select [Lab_Number],[BlockID] from [T65_Sequenom_Results] where Record_Status='Active' and mutation='BRAFmutated' and status='0'
) T20
on (T20.[Lab_Number] = T65.[Lab_Number] and T20.[BlockID]=T65.[BlockID])
Join (
select [Lab_Number],[BlockID] from [T65_Sequenom_Results] where Record_Status='Active' and mutation='KRASmutated' and status='0'
) T21
on (T21.[Lab_Number] = T65.[Lab_Number] and T21.[BlockID]=T65.[BlockID])
Join (
select [Lab_Number],[BlockID] from [T65_Sequenom_Results] where Record_Status='Active' and mutation='NRASmutated' and status='0'
) T22
on (T22.[Lab_Number] = T65.[Lab_Number] and T22.[BlockID]=T65.[BlockID])
Join (
select [Lab_Number],[BlockID] from [T65_Sequenom_Results] where Record_Status='Active' and mutation='PIK3CAmutated' and status='0'
) T23
on (T23.[Lab_Number] = T65.[Lab_Number] and T23.[BlockID]=T65.[BlockID])
) T order by T.[Lab_Number] desc
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Thank you for the reply, Yes I agree with you...
My requirement is like user will upload the excel in which there will be a lab id, block id, mutation1, value1,mutation2, value2..... mutation count will be dynamic. So in database only have one table and how many mutatiions are there for lab block combination that many rows will be for the combination. In search screen, user will select mutation and its value... foreach condition user selected have to join the table with mutation and value condition.
help me on this....
Thanks,
Praveen.
-
praveenkumarpalla (2/2/2015)
Thank you for the reply, Yes I agree with you...My requirement is like user will upload the excel in which there will be a lab id, block id, mutation1, value1,mutation2, value2..... mutation count will be dynamic. So in database only have one table and how many mutatiions are there for lab block combination that many rows will be for the combination. In search screen, user will select mutation and its value... foreach condition user selected have to join the table with mutation and value condition.
help me on this....
Thanks,
Praveen.
I can help you with the query. You will have to provide CREATE TABLE scripts and also scripts to populate the tables with sample data.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply