Urgent help required !!!!!

Question

Urgent help required !!!!!

Viewing 11 posts - 31 through 40 (of 40 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

maruf24 (8/4/2008)
i hav used exists but there is no use same result...
...... shud i do 1 thing to check the appication.... some one might changed the coding as it run through java....

Heh... I'm thinking you've just identified the source of the performance problem... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mcloney SSC Eights! Points: 885 More actions · Answer 2

Hey Again,

I'm out of the office today, but I'll try to post my code tomorrow.

Cheers,

-Matthew

samsql Hall of Fame Points: 3044 More actions · Answer 3

Wat i hav found tht there is some change in java code as i m a dba ...

..... i dnt no wat are the changes has been made but wat i think due to this.... the query is taking time performance issue ..... bcoz i hav done each and every thing from the database side still no progress ... and now i hav to send a mail to my manager .... abut this slowness as he is askin for the reason.

Pall A. Gudmundsson SSC Enthusiast Points: 107 More actions · Answer 4

Hi

If the table gets updated a lot the index might get fragmented. I had a similar problem once which got resolved when ran update statistics on the table. Try to update the statistics or even rebuild the indexes.

Also the Database Tuning Advisor can come in quite handy as it can suggest new indexes.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

maruf24 (8/5/2008)
Wat i hav found tht there is some change in java code as i m a dba ...
..... i dnt no wat are the changes has been made but wat i think due to this.... the query is taking time performance issue ..... bcoz i hav done each and every thing from the database side still no progress ... and now i hav to send a mail to my manager .... abut this slowness as he is askin for the reason.

This problem has been ongoing for at LEAST 6 days now. Had you simply hired a professional tuner to remote in and check things out the problem would likely have been identified (and if a database/server problem solved) in a matter of hours. You would gain the benefit of learning what and how from the pro as well. Please tell your manager that for me.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

samsql Hall of Fame Points: 3044 More actions · Answer 6

This issue has been resolved as there was a change in java code .... the code has been revert back .... not required a professional we hav to belive in our self ....

Thkz for each n every 1.. who has commented on this...

Special Thkz to.....

GilaMonster

WiltsDBA

TheSQLGuru

Jeff Moden

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Thank you for the feedback... glad you solved it. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Don.Watters Grasshopper Points: 18 More actions · Answer 8

Can you post the actual Query, instead of trying to mask the table and column names? It just makes things worse. Here's an attempt at fixing it, but since it's missing a paren somewhere, I don't know if I'm on the right track.

SELECTt3.id

,t3.aaa

,t3.colname

,t1.id

,t1.ttt

,t1.c_f_name

,t2.cmd

FROM table3 t3

INNER JOIN table1 t1

ON t3.colname=t1.colname

INNER JOIN table2 t2

ON t1.colname = t2.colname

AND t3.colname = t2.colname

AND t2.colname = '365464'

WHERE t1.id = '354'

AND t1.colname='A'

AND t3.colname =123

The LIKE clause, in this case, isn't providing any real difference than an equi-join would end up giving you. So, why not just put it in the JOIN clause? As for the rest of it...it's hard to tell, because there aren't any aliases on the two colname columns at the end of the query...and as we all know, 'A' does not equal 123, so, that would never return a record. So, I deduced that they were from different tables...and not from table 2, because that was already being checked with the LIKE condition.

Again, sending the real query would've probably helped....and none of what I've done will likely help. We need more info, like a query plan and the actual query, and maybe some DDL...and number of records in each table...and blah, blah, blah... 🙂

Joe Torre-370834 Grasshopper Points: 10 More actions · Answer 9

Your query would return ambiguous column "colname", also like '365464' is the same as = '365464'.

Try the WITH (NOLOCK) hint. If more than one user at a time is querying these tables SQL Server will put locks on the tables while it internally creates temp tables to speed execution. So some are waiting for others queries to finish and the tables to unlock. It is often faster to create a temp table yourself to replace the sub-select (use select distinct to avoid duplication in the result set) with an inner join on the temp table:

select distinct colname

into #tmp

from table2

where colname = '365464'

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Joe Torre (8/16/2008)
It is often faster to create a temp table yourself to replace the sub-select

Being a bit of a skeptic, let's see the test you did that proves that. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Joe Torre (8/16/2008)
If more than one user at a time is querying these tables SQL Server will put locks on the tables while it internally creates temp tables to speed execution. So some are waiting for others queries to finish and the tables to unlock.

Not exactly.

SQL does indeed put locks on data that is being selected (but it's not necessarily at a table level unless a lot of the table is affected by the query). If the query is just a select, then shared locks are placed and shared locks are compatible with other shared locks. They'll only block exclusive or update locks which are taken when the data is modified.

Not all queries require the query processor to create work tables. Generally it's queries that include sorts/distinct sorts and/or hashes (joins or aggregates). Easiest way to tell, run the query with STATISTICS IO on and see if there's a reference to a table named WorkTable. Straight selects are unlikely to require any form of internal temp table.

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