roopesh.purohit (12/30/2009)

---

Thanks Peter - nice article.

I have a cross tab reporting project in SSRS 2005, I need to convert the survey responses/answeres and questions text into cross tab format...

Hi,

Take a look at my blog entry here.

If you are on 7 or 2000 , you will have to use an identity column but the theory is the same.

Will need to be a full outer join.....

something like this ?

with cteUnion()

as

(

Select query,querycount from jan

union all

Select query,querycount from feb

union all

Select query,querycount from mar

)

Select query,sum(querycount)

from cteUnion

group by query

Are you passing search arguments as parameters ?

If so you should be able to duplicate the poor performance using variables.

In any case you will need to post the execution plan...

Assume not and update them.

See the links in my sig below on how to help us to help you...

Why are you so intent on storing all the data for all the reports in a single table ?

Why not a table per report ?

Please see the section "Errors Unaffected by a TRY…CATCH Construct"

From http://msdn.microsoft.com/en-us/library/ms175976.aspx

"Whenever I see a table with repeating column names appended with numbers, I cringe in horror."

http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

Something like this ???

with cteRobberies

as(

Select StoreId,RobberyDate ,Row_number() over (partition by StoreID order by RobberyDate desc) as Rown

from YourTable

)

Select StoreId,min(case when Rown = 1 then RobberyDate else null end),

...

Depends if you want optimistic or pessimistic locking.

Sounds like you want to be pessismistic so sp_applock , may be best for you.

As for messaging back to a client app that...

The estimated row counts are very different , are your statistics up to date ?

Check this page in the section chapter "Increasing Full-Text Query Performance" for some good info

http://technet.microsoft.com/en-us/library/cc721269.aspx

Besides which,...

No worries , glad to help

Try using the 'OPTIMIZE FOR' Query hint just to see if that will create a good plan.

Is there something 'unusual' about the IDContract value ?