June 12, 2012 at 12:18 pm
If I have following environment
Stock (Stockid(PK), Stockname, Price) -Have 2millions rows
Sales(SalesId(PK), Sockid(FK), DateOfsales) - have 1.5 millions rows
Since both table have index, Let say RAM and Processor are also reasonable
Select StockName, Price, Dateofsales from Stock join Sales on Stock.stockid = Sales.stockid
Let's say it take 30 minutes, What other way I can increase Performance or retrive data in less then 30 minutes.
June 12, 2012 at 12:22 pm
Please post the create statement for the tables and the indexes (data types are important). If this is an existing query, posting the actual execution plan would also be very helpful.
June 12, 2012 at 12:27 pm
Dear David,
I am need in SQL Server, it was asked in interview.
June 12, 2012 at 12:35 pm
30 minutes is a long time for the volume of data you are talking about:
Check to see if the data is spread across the disks appropriately and that disk contention isn't slowing you down.
Think about redefining the indexes to include the columns from the query so the index covers the query.
Check for any blocking that might be happening as the query runs.
Make sure the statistics are updated.
I'm sure others will have other suggestions...
June 12, 2012 at 1:04 pm
Munabhai (6/12/2012)
Dear David,I am need in SQL Server, it was asked in interview.
I have a question, what answer did you give in the interview? To be honest, I personally have a problem helping with answers to interview questions, but I'm willing to help guide you to other possible answers without giving you a direct answer to your question.
June 12, 2012 at 3:19 pm
If I was asked that question in an interview I would hope the person asking is ready for a deluge of questions coming back. There is not much to go on so I would be asking questions about datatypes, who is consuming this data, statistics on the tables. Is this a proc or an ad hoc query? This question does not have an answer. It is designed to have a discussion into how YOU go about solving performance issues. What I say and do is likely to be different than everybody else on the planet. I may do many of the same steps as lots of other people but they may be in a different order.
What was your answer?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2012 at 3:31 pm
Since I am new, I did say I will use Index. Then I said I will look at execution plan, and figure out why it is taking so long time. And he asked me, cross question ok even if you see CPU Time is high in one of join then what will you do after finding out where there is problem?
June 12, 2012 at 3:33 pm
And your response? Again there is no correct answer, except to be honest.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2012 at 5:18 pm
As I said I am new so, I answered what I knew. I know that not fully correct or that's partially correct. After that He move different question.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply