stored proc taking too long!

Question

Post reply

stored proc taking too long!

Viewing 15 posts - 31 through 45 (of 46 total)

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 1

Grant,

Today is Truth Day n Explanation Day......hehhehe..lolz;)

Would you like to Explain What you told Now......?:w00t:

Holy cow! You guys posted all that in the time it took me to read the first couple of posts. Slow down doggone it. You're making me look more stupid than usual!

:hehe:

Cheers!

Sandy.

--

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 2

Hi gail

Thanks a lot for the explanation, and Sandy for trying 🙂

I will read more on this ROW_NUMBER function, since it seems pretty cool!

Thanks all for helping!

Johann

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 3

Gail

Another quick question, how do I reset TheOrder to 1 again. Because when I run the script more than once, its starting from for exampl 953

Thanks

Johann

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 4

Gail,

Please Give the Answer......

Otherwise it will be reset the row_number value......to "O"

Lolz......:w00t::P:D:cool::hehe:

Cheers!

Sandy.

--

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

It shouldn't. Rownumber starts at 1, regardless of how many times you run it. It's the row numbers for the current query, nothing more. Each time the partition columns changes it should go back to 1 as well.

Sound almost like there's an identity involved somewhere.

create table #temp(

[Id] int IDENTITY (1, 1) NOT NULL ,

[langCode] char(6),

[projectid] int,

[status] int,

[Order] int

)

INSERT INTO #Temp (LangCode, ProjectID) VALUES ('en', 1)

INSERT INTO #Temp (LangCode, ProjectID) VALUES ('fr', 1)

INSERT INTO #Temp (LangCode, ProjectID) VALUES ('en', 2)

INSERT INTO #Temp (LangCode, ProjectID) VALUES ('fr', 2)

-- When I run this, no matter how many times, it returns the same value for TheOrder

SELECT ID, langCode, projectid,

ROW_NUMBER() OVER (PARTITION BY projectID, langCode ORDER BY projectID, langCode) AS TheOrder

FROM #temp

-- run this 10 times

UPDATE #temp SET [Order] = TheOrder

FROM (SELECT ID, ROW_NUMBER() OVER (PARTITION BY projectID, langCode ORDER BY projectID, langCode) AS TheOrder

FROM #temp) Sub

WHERE #temp.ID = Sub.ID

-- AND then select from the temp table

SELECT * FROM #Temp

Returns the following no matter how many times I run the update.

Id langCode projectid status Order

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

1 en 1 NULL 1

2 en 1 NULL 2

3 fr 1 NULL 1

4 fr 1 NULL 2

5 fr 1 NULL 3

6 en 2 NULL 1

7 en 2 NULL 2

8 en 2 NULL 3

9 fr 2 NULL 1

10 fr 2 NULL 2

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Johann, have you come right?

If not, please post some code that reproduces the problem (complete with table creation script and inserts for sample data) and I'll see if I can figure out what's wrong.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 7

Hi gail

Sorry was in a meeting

Let me check ok, will let you know soon

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 8

Hi Gail

Yeah the order is starting from 1, its something in my ordering that is not working

Thanks

Regards

Johann

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

I took Gail's code and made some changes. Go ahead and check it out.

create table #Temp(

[Id] int IDENTITY (1, 1) NOT NULL ,

[LangCode] char(6),

[ProjectID] int,

[status] int,

[Order] int,

primary key (Id)

);

create index IX1_Temp on #Temp (ProjectID, LangCode);

INSERT INTO #Temp (LangCode, ProjectID) VALUES ('en', 1)

INSERT INTO #Temp (LangCode, ProjectID) VALUES ('fr', 1)

INSERT INTO #Temp (LangCode, ProjectID) VALUES ('en', 2)

INSERT INTO #Temp (LangCode, ProjectID) VALUES ('fr', 2)

select * from #Temp order by Id

declare @order int,

@langcode char(6),

@projid int;

set @order = 0;

set @langcode = '';

set @projid = 0;

update #Temp set

@order = [Order] = (case when @langcode <> LangCode or @projid <> ProjectID then 0 else @order end) + 1,

@langcode = LangCode,

@projid = ProjectID

from

#Temp with (index(IX1_Temp))

select * from #Temp order by Id

drop table #Temp

😎

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 10

Hi Lynn

your code is cool too but is taking 20 seconds, while gail's is taking just 12 seconds 🙂

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

Use what works best. I just thought I'd throw another way to do it your way.

😎

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 12

Johann Montfort

Hall of Fame

Points: 3123

May 20, 2008 at 9:55 am

#818045

thanks a lot

much appreciated;)

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 13

Gail

on the same bases, how do I do an insert if a filename changes.

For example I have a list of records, lets say 100000, and every 1000 rows, the filename is changing. I want to capture that filename and insert it in a new table. However 1000 is random.

I was thinking of something like this:-

INSERT INTO #tempFiles

SELECT eFileName,

ROW_NUMBER() OVER (PARTITION BY eFileName ORDER BY eFileName) AS TheOrder

FROM #tempContent

This does not work however

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

Johann - what does "it's not working" mean in the latest scenario. What are you hoping to get? I have a theory what it is - but instead of muddying the waters with a theory - just try to give us an example of what you're getting, and what you'd LIKE to get.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 15

Hi matt

sample:-

ID string FileName FileNumber

1 AAA file1.txt

2 ABB file1.txt

3 ABC file2.txt

4 ABD file1.txt

5 ABE file1.txt

6 ABF file3.txt

7 ABG file4.txt

8 ABH file4.txt

9 ABI file4.txt

What I wish to achieve is, once the filename changes, I insert the filename into a new table, #tempFileName, and get the Scope_Identity, and update the same fields with the file Number

Thanks

Johann