|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
too long...this is my request: declare @id_min_inscrit int declare @id_max_inscrit int select @id_min_alerte = min(cast(IDas int)) from TABLE select @id_max_alerte= max(cast(ID as int)) from TABLE set @cpt_id = @id_min_alerte while @cpt_id < = @id_max_alerte Begin set @cpt_id=@cpt_id+1 end the problem it's very long 3 minutes!!!(40000 lines!!!) why , is thre a way to optimize? because i want to add any requests for traitment. thanks in advance for your help if i add
select cast([ID] as int from IMPORT_AVAL_2 with(nolock) where cast(id as int)=@cpt_id Begin here -> set @cpt_id=@cpt_id+1 end 6 minutes!!!! Show quote "Marco" wrote: > hello all, > > this is my request: > declare @id_min_inscrit int > declare @id_max_inscrit int > > select @id_min_alerte = min(cast(IDas int)) from TABLE > select @id_max_alerte= max(cast(ID as int)) from TABLE > > set @cpt_id = @id_min_alerte > > while @cpt_id < = @id_max_alerte > Begin > > set @cpt_id=@cpt_id+1 > end > > the problem it's very long 3 minutes!!!(40000 lines!!!) > why , is thre a way to optimize? > because i want to add any requests for traitment. > > thanks in advance for your help > > > > > > > > Can you tell us what your requirement is?
> > while @cpt_id < = @id_max_alerte What is the use of this while loop?> > Begin > > > > set @cpt_id=@cpt_id+1 > > end > > What do you want to achieve? > declare @id_min_inscrit int What data type is ID? Is the CAST necessary? Is there an index? Why do > declare @id_max_inscrit int > > select @id_min_alerte = min(cast(IDas int)) from TABLE > select @id_max_alerte= max(cast(ID as int)) from TABLE this in two queries when you can do it in one? SELECT @id_min_alerte = MIN(ID), @id_max_alerte = MAX(ID) FROM TABLE; Then I'm not sure I understand the loop. What are you counting / adding? A What data type is IDas? It seems you could do something like:
SELECT CAST(MAX(IDas) - MIN(IDas) + 1 AS int) FROM dbo.MyTable -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Marco" <Ma***@discussions.microsoft.com> wrote in message news:106094C6-DFA9-4FC6-8007-DE2AE58E5659@microsoft.com... > hello all, > > this is my request: > declare @id_min_inscrit int > declare @id_max_inscrit int > > select @id_min_alerte = min(cast(IDas int)) from TABLE > select @id_max_alerte= max(cast(ID as int)) from TABLE > > set @cpt_id = @id_min_alerte > > while @cpt_id < = @id_max_alerte > Begin > > set @cpt_id=@cpt_id+1 > end > > the problem it's very long 3 minutes!!!(40000 lines!!!) > why , is thre a way to optimize? > because i want to add any requests for traitment. > > thanks in advance for your help > > > > > > > > Marco
Can yopu elaborate a little bit? > select @id_min_alerte = min(cast(IDas int)) from TABLE select @id_min_alerte = min(cast(IDas int)),@id_max_alerte= max(cast(ID as > select @id_max_alerte= max(cast(ID as int)) from TABLE int)) from TABLE Show quote "Marco" <Ma***@discussions.microsoft.com> wrote in message news:106094C6-DFA9-4FC6-8007-DE2AE58E5659@microsoft.com... > hello all, > > this is my request: > declare @id_min_inscrit int > declare @id_max_inscrit int > > select @id_min_alerte = min(cast(IDas int)) from TABLE > select @id_max_alerte= max(cast(ID as int)) from TABLE > > set @cpt_id = @id_min_alerte > > while @cpt_id < = @id_max_alerte > Begin > > set @cpt_id=@cpt_id+1 > end > > the problem it's very long 3 minutes!!!(40000 lines!!!) > why , is thre a way to optimize? > because i want to add any requests for traitment. > > thanks in advance for your help > > > > > > > > > Transact-SQL, the dialect of SQL used by Microsoft SQL Server, is an
efficient tool for relational processing. It is not well optimized for looping and processing things one row at a time. There is an excellent chance that the part you did not include - the rest of what happens in the loop - can be rewritten so that you do not need any loop. Stated another way, the work can probably be done relationally, processing all the rows in a single command, instead of procedurally, one row at a time as in the sample code. If you explain what needs to be done in that code someone here may be able to give some constructive advice. For a better idea of what to include, see http://www.aspfaq.com/5006. Roy Harvey Beacon Falls, CT On Thu, 27 Jul 2006 05:09:02 -0700, Marco <Ma***@discussions.microsoft.com> wrote: Show quote >hello all, > >this is my request: >declare @id_min_inscrit int >declare @id_max_inscrit int > >select @id_min_alerte = min(cast(IDas int)) from TABLE >select @id_max_alerte= max(cast(ID as int)) from TABLE > >set @cpt_id = @id_min_alerte > >while @cpt_id < = @id_max_alerte >Begin > > set @cpt_id=@cpt_id+1 >end > >the problem it's very long 3 minutes!!!(40000 lines!!!) >why , is thre a way to optimize? >because i want to add any requests for traitment. > >thanks in advance for your help thanks for your response for all
i have atable which contains 40000 lines but begins with an ID =120 and finished by 250000 for example i loop all line for get information and effectuate a traitment if col001= ... begin... end the problem it is veruy long PS: i convert because Id on my table is varchar and my counter is int that's why i made the conversion Show quote "Roy Harvey" wrote: > Transact-SQL, the dialect of SQL used by Microsoft SQL Server, is an > efficient tool for relational processing. It is not well optimized > for looping and processing things one row at a time. > > There is an excellent chance that the part you did not include - the > rest of what happens in the loop - can be rewritten so that you do not > need any loop. Stated another way, the work can probably be done > relationally, processing all the rows in a single command, instead of > procedurally, one row at a time as in the sample code. If you explain > what needs to be done in that code someone here may be able to give > some constructive advice. For a better idea of what to include, see > http://www.aspfaq.com/5006. > > Roy Harvey > Beacon Falls, CT > > On Thu, 27 Jul 2006 05:09:02 -0700, Marco > <Ma***@discussions.microsoft.com> wrote: > > >hello all, > > > >this is my request: > >declare @id_min_inscrit int > >declare @id_max_inscrit int > > > >select @id_min_alerte = min(cast(IDas int)) from TABLE > >select @id_max_alerte= max(cast(ID as int)) from TABLE > > > >set @cpt_id = @id_min_alerte > > > >while @cpt_id < = @id_max_alerte > >Begin > > > > set @cpt_id=@cpt_id+1 > >end > > > >the problem it's very long 3 minutes!!!(40000 lines!!!) > >why , is thre a way to optimize? > >because i want to add any requests for traitment. > > > >thanks in advance for your help > Can you give the ddl, sample data and result you need with a small
explaination on the calculation that you perform. Maybe you need to loop through it line by line > Maybe you need to loop through it line by line Did you mean "Maybe you /don't/ need to loop ..."?oops.. of course.. Thats what I meant :)
Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > Maybe you need to loop through it line by line > > Did you mean "Maybe you /don't/ need to loop ..."? > > > Marco (Ma***@discussions.microsoft.com) writes:
> thanks for your response for all When you say: > > i have atable which contains 40000 lines but begins with an ID =120 and > finished by 250000 for example > i loop all line for get information and effectuate a traitment > if col001= ... begin... end > > the problem it is veruy long > > PS: i convert because Id on my table is varchar and my counter is int > that's why i made the conversion select cast([ID] as int from IMPORT_AVAL_2 with(nolock) where cast(id as int)=@cpt_id and there is an index on id, that index helps you none if you cast the varchar to int, because character data and index data sorts differently. That's a scan instead of index seek. There you have a good deal of explanation of why it takes time. Say WHERE id = CAST (@cpt_id AS varchar) instead. Then again, this can fail if there are leading zeroes etc. Anyway, normally in SQL programming you don't use loops - you process all rows at once. When you process rows one by one, it usually takes a lot more time. If there are 40 rows you can get away with it, but for 40000 rows you will have to pay dearly. But if you don't tell us what is going on in this loop, we cannot assist you with knowing what you are doing. You could at least start with posting the full code of the loop. And please in keep in mind that we know SQL in this place, we know very little about your business problem. Simply saying "traitment" just leaves in the dark. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx that's unbelievable is a other traitmeent with the same code
is ok with 60000 lines in 2-3 minutes the tables are the same type the code is the same no index in two tables why this difference with the time? Show quote "Marco" wrote: > hello all, > > this is my request: > declare @id_min_inscrit int > declare @id_max_inscrit int > > select @id_min_alerte = min(cast(IDas int)) from TABLE > select @id_max_alerte= max(cast(ID as int)) from TABLE > > set @cpt_id = @id_min_alerte > > while @cpt_id < = @id_max_alerte > Begin > > set @cpt_id=@cpt_id+1 > end > > the problem it's very long 3 minutes!!!(40000 lines!!!) > why , is thre a way to optimize? > because i want to add any requests for traitment. > > thanks in advance for your help > > > > > > > > You need to show us all of the code. I highly doubt it takes SQL Server
more than two minutes to increment a counter by 1 40,000 times. On my desktop SQL Server can perform this task in 14 seconds. So, what else you're doing in that loop is probably the most important part! I also think you need to show us the actual structure of the tables, incuding data types, keys, indexes, constraints -- possibly even triggers, if the missing code touches the table within the loop. A Show quote "Marco" <Ma***@discussions.microsoft.com> wrote in message news:2079B48D-DCAF-4EA2-9506-BA93656AE75C@microsoft.com... > that's unbelievable is a other traitmeent with the same code > is ok with 60000 lines in 2-3 minutes > the tables are the same type > the code is the same > no index in two tables > > why this difference with the time? > > > "Marco" wrote: > >> hello all, >> >> this is my request: >> declare @id_min_inscrit int >> declare @id_max_inscrit int >> >> select @id_min_alerte = min(cast(IDas int)) from TABLE >> select @id_max_alerte= max(cast(ID as int)) from TABLE >> >> set @cpt_id = @id_min_alerte >> >> while @cpt_id < = @id_max_alerte >> Begin >> >> set @cpt_id=@cpt_id+1 >> end >> >> the problem it's very long 3 minutes!!!(40000 lines!!!) >> why , is thre a way to optimize? >> because i want to add any requests for traitment. >> >> thanks in advance for your help >> >> >> >> >> >> >> >>  |
|||||||||||||||||||||||
Other interesting topics