StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
10026112
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
5
CommunityOwnedDate
CreationDate
2012-04-05T09:32:47.077
FavoriteCount
0
LastActivityDate
2015-02-19T07:46:21.123
LastEditDate
2017-05-23T12:26:27.260
LastEditorUserId
-1
OwnerUserId
1483
ParentId
10025569
PostTypeId
2
Score
49
ViewCount
0
LastEditorDisplayName
text
Body
There are a few issues with this query (and this apply to every query). <h1>Lack of index</h1> Lack of index on <code>er101_upd_date_iso</code> column is most important thing as <a href="https://stackoverflow.com/users/1583/oded">Oded</a> has already mentioned. Without matching index (which lack of could cause table scan) there is no chance to run fast queries on big tables. If you cannot add indexes (for various reasons including there is no point in creating index for just one ad-hoc query) I would suggest a few workarounds (which can be used for ad-hoc queries): <h2>1. Use temporary tables</h2> Create temporary table on subset (rows and columns) of data you are interested in. Temporary table should be much smaller that original source table, can be indexed easily (if needed) and can cached subset of data which you are interested in. To create temporary table you can use code (not tested) like: <pre><code>-- copy records from last month to temporary table INSERT INTO #my_temporary_table SELECT * FROM er101_acct_order_dtl WITH (NOLOCK) WHERE er101_upd_date_iso > DATEADD(month, -1, GETDATE()) -- you can add any index you need on temp table CREATE INDEX idx_er101_upd_date_iso ON #my_temporary_table(er101_upd_date_iso) -- run other queries on temporary table (which can be indexed) SELECT TOP 100 * FROM #my_temporary_table ORDER BY er101_upd_date_iso DESC </code></pre> Pros: <ul> <li>Easy to do for any subset of data.</li> <li>Easy to manage -- it's temporary and it's table.</li> <li>Doesn't affect overall system performance like <code>view</code>.</li> <li>Temporary table can be indexed.</li> <li>You don't have to care about it -- it's temporary :).</li> </ul> Cons: <ul> <li>It's snapshot of data -- but probably this is good enough for most ad-hoc queries.</li> </ul> <h2>2. Common table expression -- CTE</h2> Personally I use <a href="http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx" rel="noreferrer">CTE</a> a lot with ad-hoc queries -- it's help a lot with building (and testing) a query piece by piece. See example below (the query starting with <code>WITH</code>). Pros: <ul> <li>Easy to build starting from big view and then selecting and filtering what really you need.</li> <li>Easy to test.</li> </ul> Cons: <ul> <li>Some people dislike CDE -- CDE queries seem to be long and difficult to understand.</li> </ul> <h2>3. Create views</h2> Similar to above, but create views instead of temporary tables (if you play often with the same queries and you have MS SQL version which supports indexed views. You can create views or <a href="http://msdn.microsoft.com/en-us/library/dd171921(v=SQL.100).aspx" rel="noreferrer">indexed views</a> on subset of data you are interested in and run queries on view -- which should contain only interesting subset of data much smaller than the whole table. Pros: <ul> <li>Easy to do.</li> <li>It's up to date with source data.</li> </ul> Cons: <ul> <li>Possible only for defined subset of data.</li> <li>Could be inefficient for large tables with high rate of updates.</li> <li>Not so easy to manage.</li> <li>Can affect overall system performance.</li> <li>I am not sure indexed views are available in every version of MS SQL.</li> </ul> <h1>Selecting all columns</h1> Running star query (<code>SELECT * FROM</code>) on big table is not good thing... If you have large columns (like long strings) it takes a lot of time to read them from disk and pass by network. I would try to replace <code>*</code> with column names which you really need. Or, if you need all columns try to rewrite query to something like (using common data expression): <pre><code>;WITH recs AS ( SELECT TOP 100 id as rec_id -- select primary key only FROM er101_acct_order_dtl ORDER BY er101_upd_date_iso DESC ) SELECT er101_acct_order_dtl.* FROM recs JOIN er101_acct_order_dtl ON er101_acct_order_dtl.id = recs.rec_id ORDER BY er101_upd_date_iso DESC </code></pre> <h1>Dirty reads</h1> Last thing which could speed up the ad-hoc query is allowing dirty reads with <a href="http://msdn.microsoft.com/en-us/library/ms187373.aspx" rel="noreferrer">table hint <code>WITH (NOLOCK)</code></a>. Instead of hint you can <a href="http://msdn.microsoft.com/en-us/library/ms173763.aspx" rel="noreferrer">set transaction isolation level</a> to read uncommited: <pre><code>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED </code></pre> or set proper SQL Management Studio setting. I assume for ad-hoc queries dirty reads is good enough.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POImprove SQL Server query performance on large tables
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USCommunity
UserOwnerUserId
1. USGrzegorz Gierlik
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.