Post reply

view with 125 columns

  • May 10, 2011 at 3:20 am

    #99297

    I have a view which has more than 125 columns. The view is based on joining of 5 tables. teh data may be more than 10000. All of the columns are based on some calculation like case statement/max(). I am worroying that iin near future there will a severe performance issue.

    Could you suggest anything to avoid the situation?

  • May 10, 2011 at 6:38 am

    #1322239

    performance can be a big question;

    At least for a start, grab the query portion of the view, and run it in SSMS.

    Look at the execution plan; see if any additional indexes, say on the columns featured in the JOIN/WHERE will make a difference;

    most likely, the view will be queries with additional filtering criteria with a WHERE statement, right?

    Can you make sure the arguments are SARG-able via the application that hits the view? if you know which columns will be used in the WHERE statement, you could add indexes on those columns on the base tables to assist performance.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 11, 2011 at 1:08 am

    #1322772

    Hi Lowell,

    Indexing probably not solve our problem as all the fields are well indexed. What initially I have thought is:

    Splitting the view with 4-5 views, containing 40 columns each and create a 1: 1 relationship between them. I am seeing the advange here because at a time all columns will not be processed. ONly few columns will be processed. It seems it will increase the performance.

    Please provide your comment.

    Thanks,

    Arup

  • May 12, 2011 at 5:37 am

    #1323591

    Hi

    Is there any update on it? Or this is an entirely new scenario 😀

  • May 12, 2011 at 5:41 am

    #1323593

    One more thing, this is entirely related to future performance. If you guys fill that there will not be any performance issue, then please let me know.

    I think, since this is a view and not a table, sence even I need only onr column data, then also entire view SQL will be executed and will send me the required view data...is it true? Or it is like that only the underlying SQL will be executed once and stored somewhere inside the memory and once I will call any view column, the whole SQL will not be executed and send the data to me? If it is so, then I may not have performance issue from columns.

    Please put soem light how the view is working.

    Regards,

    Arup

Viewing 5 posts - 1 through 4 (of 4 total)

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