SQLKnowItAll (3/28/2012)

---

If someone asks you to make a car fly by only changing the fuel or the engine, could you do it? You may get it to go fast enough...

Sean Lange (3/28/2012)

---

You asked for some advice on structuring your data better and I was trying to figure out why every table and column have names that are only 6...

SQLKnowItAll (3/28/2012)

---

You may not want to hear this... but I suggest hiring a consultant who can sit down and understand the business requirements and design this properly. We won't be...

I modified the query as you friends helped me...the below query works really great when compared to old one....but having loadz of queries that needs to modify with all of...

Sean Lange (3/28/2012)

---

Do you have to interface with a mainframe or some other older system? The reason I ask is all of your names are so short. It is nearly...

GilaMonster (3/28/2012)

---

It's a suggestion. Afterall, you said "the table structure is so stupid so that we are not able to get...

you want me to redesign the table?

The problem is there is no primary key columns or any unique columns, no index in any of the tables that i deal with...

only very few distinct values in columns...

the...

Sorry about that Sean..

Here i attached ...

SQLKnowItAll (3/27/2012)

---

How long does it take to just execute your cte portion?

SELECT --COUNT(*)

j.NameIDRef

, 0 AS SEQNUM

, ADR.GASTR# AS PREMISE

, A.PnxCodeValue AS PREFIX

, ADR.GASNAM AS STREETNAME

, B.PnxCodeValue as STREETTYPE

, ADR.GASQLF AS...

The problem is it is taking more than 1 hour to execute the query...but the same query takes more than 12 hours on production server..

i have no idea why it...

I am not able to get the execution plan with data since it is taking too much time...

Sean Lange (3/27/2012)

---

Charmer (3/27/2012)

---

Sean Lange (3/27/2012)

---

nvarchar(1)... not actually a performance issue but really???

All columns nullable...

No primary keys...

No indexes at all on at least 1 of the tables...

You pick the performance...

Sean Lange (3/27/2012)

---

nvarchar(1)... not actually a performance issue but really???

All columns nullable...

No primary keys...

No indexes at all on at least 1 of the tables...

You pick the performance problems at the...

SQLKnowItAll (3/27/2012)

---

You use ROW_NUMBER(...) AS seqnum. Then you join and filter the CTE using WHERE seqnumber = 0. What a waste! Just find the 1 row you want from...