I M basically an application developer & use simple sql queries in my programmings. I do not have much idea abt tuning/auditing part & thatswhy i m unable to answer them properly in my interviews. Can anybody give me some tips???
Question 1:
In a stored procedure, One SELECT stmt is there & depending upon the @.rowcount, it updates around 14000 records which is also written inside this stored procedure. Instead of writing this way, there is some other way which is faster than this. Can anybody tell me the correct way?
Question 2:Can anybody give me few examples like this?? I need them desparetly.
Thanx. Bye.::I M basically an application developer & use simple sql queries in my programmings. I do
::not have much idea abt tuning/auditing part & thatswhy i m unable to answer them
::properly in my interviews. Can anybody give me some tips???
Get a good beginner book into SQL, like "SQL for Dummies" (no pun intended, the dummies books are nowmally very nice).
::In a stored procedure, One SELECT stmt is there & depending upon the @.rowcount, it
::updates around 14000 records which is also written inside this stored procedure. Instead of
::writing this way, there is some other way which is faster than this. Can anybody tell me the
::correct way?
No. Because you do not give enough information to advice on a better way.
::Question 2:Can anybody give me few examples like this?? I need them desparetly.
I really suggest some introductory books about how SQL works. Simple queries do not cut it.|||Let me give the skeleton of the store proc:
select * from tab1
if @.rowcount>1
update table2 set col1="asadsasasa"
end if
That person was asking me "by updating 40000 records in this manner, it will be quite slow". So, what is the faster way?
thanx for ur advice... i w'll go thru one such book.
Bye.|||Another rubbish question, the original question that is. What does "updating 40K records" mean? Are they saying you'd call this proc 40K times? I assume they're talking about an update with a join, but the question is just awful. The proc shown here would always just update the same thing so an update join would be no better? Madness!|||::That person was asking me "by updating 40000 records in this manner, it will be quite
::slow". So, what is the faster way?
The issue is the
::select * from tab1
which retrieves the rows.
a better way is along the lines
select count(*) from tab1 (hm, how do syou select into a variable- i Havenot done sp's for more than a year)
if (variable) > 1
the select count(*) does not retrieve the values.
You should really get:
* SQL for dummies
THEN
SQL for Smarties from Joe Celko|||Well done thona for translating the question! If you're right then you'd be better of with an "exists"
If exists(select * from tab1)
update table2 set col1="asadsasasa"
Although I still think the wording of "update 40000..." is mighty odd.|||::If you're right then you'd be better of with an "exists"
Yes, I realized this, too, once it was posted.
Bout someone else was right - the original question is not formulated good.|||Thanx everybody & extremely sorry for these type of questions. But hope u all understand my problem. Actually all these questions are asked by somebodyelse. So, thanx for ur cooperation.
Yes, Exists & Count are better option for the "SELECT" stmt. But he was stressing more on the "UPDATE" stmt which is updating 14K records after getting the @.ROWCOUNT.
SO KINDLY COOPERATE AGAIN.
THNX EVERYBODY.|||If you really want to change the same col in 40K records there really isn't much else you can do. Perhaps turn off the indexing and put it back at the end or something but I think that's reaching a bit. If that represented the entire table then I 'spose you could argue that truncating the table and then inserting might be a tad quicker but again that sounds a mighty complicated answer for such a strange question. Plus you'd get into trouble if you wanted to rollback. Are you sure there isn't something missing from the original question?|||Hi PKR!
thnx for ur cooperation. There is no other clue for u/me. He was only stressing on that point only.(i.e. updating 14k records in this manner will take lot of time).
Do u have any idea abt the followings:
1. Is there any possibility to fire a trigger for updation?
2. Is a nested stored proc will be a better option?
Thanx again.
Waiting for reply...|||I already said once, I repeast it now:
get an introducotry book into SQL. Most of your questions are simply a result of not really having a deep understanding of the language. You need to get into set oriented mood to achieve anything in SQL in a fast way.
::1. Is there any possibility to fire a trigger for updation?
Did you look at the documentation of the CRATE TRIGGER statement? It would answer this question in less time than posting.
::2. Is a nested stored proc will be a better option?
This sentence makes no sense grammar wise, sadly.
Nested SP's should be used when necessary. Calling a nested SP instead of making a set operation is a beginner error, though - a proper SQL Server (count oracle out of this for certain things) works SET oriented. Replacing a set command with a nested SP called in a loop is NOT a good way to achieve something.|||Hi!
Sorry ... & thanx for ur valuable advice. I'll do as u said.
Bye|||Hello Thomas!
Sorry for disturbing u again. AS per ur advice, i went thru the fundas.
It seems that fellow wanted to ask me the alternative of "Runtime Query Handling" in a stored proc. As it will be slow if we use the runtime queries ....
So, cud u pls. tell me if any alternative approach is there. I think only alternative approach is to do these things in our application program instead of stored proc.
Kindly advice.
Reagrds,
Lita.|||::It seems that fellow wanted to ask me the alternative of "Runtime Query Handling" in a
::stored proc.
Alternative of WHAT?
Puting three terms noone knows besides you into paranthesis does not make it common knowledge.
What do you mean?|||Interesting that neither "Run-time Query Handling" nor "Runtime Query Handling" return a single hit from Google. Sounds like an interviewer who doesn't have a clue and is trying to throw a curve.
