November 25, 2009 at 10:42 pm
SELECT PageId, PagePath, Status, AlterPath,PageDBId from PageRep
This query fires from Application - for every user it fires many times, users are of 1500+ and the hits will be of 15000 per day on the table.
only 5 coulmns, 342 rows , Indexes applied for this tablebut still its s costliest query.
Clustered Index Scan(OBJECT: [PageRepo].[PK_PageRep])
Can anyone please tell the better solution to avoid this..
Cheers,
- Win.
" Have a great day "
November 25, 2009 at 11:02 pm
Well, the query's asking for all the columns and all the rows in the table, so there's no real way to optimise. You're asking for the entire table, so a table scan (clustered index scan is a table scan) is about the only way.
Do you really need all the rows in the 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
November 25, 2009 at 11:16 pm
Agree.
The requester can also go ahead and look for the granular level as how the table is created. Can you tell me the table structure?
November 26, 2009 at 12:17 am
Thanks for the reply,
this table has the column page path, this column navigates the application for the required selection on the page. Not for all the users - the selection of all rows needed. Super , test, read are the user types on the application page. So super user need all the rows to be selected to navigate whole application tabs and the processes to be done.
There is nothing like a structure more:
Just a table PageRep - 5 columns - 342 rows (these are constant values, never get updated)
This table get selected executed when the application is browsed for all the users. i applied indexing but still its a costly query.
pls let me know if anything else to be provided.
Cheers,
- Win.
" Have a great day "
November 26, 2009 at 1:12 am
If you have to select all the rows everytime then better not to use any index.
I asked you the columns and size for your table. ie table script
eg: Table: Contact
Contactid - smallint
Contact Name - Varchar(100) etc...
Drop me this info once.
November 26, 2009 at 2:48 am
winslet (11/26/2009)
i applied indexing but still its a costly query.
Indexing won't help in the slightest when you're querying all columns and all rows.
The users who don't need all the rows, what does their query look like, what's the table structure and what indexes do you have?
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
November 26, 2009 at 3:57 am
winslet (11/26/2009)
Thanks for the reply,this table has the column page path, this column navigates the application for the required selection on the page. Not for all the users - the selection of all rows needed. Super , test, read are the user types on the application page. So super user need all the rows to be selected to navigate whole application tabs and the processes to be done.
There is nothing like a structure more:
Just a table PageRep - 5 columns - 342 rows (these are constant values, never get updated)
If possible try to handle this scenario in two ways - one for specific user, for that you can provide user information in the where clause.
For the super user use case,try to see if caching of these complete table is feasible from application as you have mentioned only few rows in the table. That will avoid hit to db.
November 26, 2009 at 11:53 am
I know that what I am suggesting is not a query optimization method. You stated that the results never change. It is a static table. Why not just load it once when the application is opened and then keep it in memory. Or if it is a web application why not put the data into the server as an application variable that is flushed once very 30 minutes?
-Roy
November 26, 2009 at 9:39 pm
Good point Roy. Could you elaborate a bit as how to keep data in memory and use that? Are you talking from front end point of view? I am interested to listen to you.
November 27, 2009 at 4:57 am
Yes, I was talking about from the application layer or the middle tier if you have one. You could also use packages like Memcache to do these kind of stuff. The best way to deal with DBs are to have a middle tier and keep the business logic out of the database.
Like I said, if it is a web application, you can either use application variables to store the data or Memcache. If it is a desk top application, when the app is loaded put it into memory.
-Roy
December 1, 2009 at 9:37 pm
Sorry to all,
Was out of station.
Yes its an application which runs on .NET and Framework.
Table has one cluster(PK) and one non cluster index. For every user this table hits by a procedure for a home page of my application.
Cheers,
- Win.
" Have a great day "
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply