63 reputation
6
bio website wds-it.com
location Olathe, KS
age 36
visits member for 1 year, 11 months
seen Jul 9 at 17:17

Coder for 17+ years.


Aug
31
comment Use MySQL to regularly do multi-way joins on 100+ GB tables?
@RickJames Agreed. You must take into account the amount of inserts for this kind of strategy and how fast they must process.
Aug
31
comment Use MySQL to regularly do multi-way joins on 100+ GB tables?
@xnickmx If there aren't so many insert/updates, triggers make it easy/performant to keep denormalized data in sync. If it needs to go faster for inserts/updates, queue them with something like this: blog.shlomoid.com/2008/04/… or bake your own. This way you don't have to join against the existing 100 million row tables to get the new data since when the trigger fires, you take advantage of the fact you know the new data right then and can just denormalize it as part of the tx or queue it for denormalization later.
Aug
29
awarded  Teacher
Aug
29
answered Use MySQL to regularly do multi-way joins on 100+ GB tables?
Aug
29
comment Get and count all blog posts and all users that fits a LIKE condition (two sepparated queries)
@ReynierPM It will use the column name from the first select. However, UNION selects only distinct records. I haven't tried it like this, but I suspect that it may return only one record if the values are equal. You probably want to use UNION ALL. Also, you can SELECT SUM(nm_rows) FROM (SELECT COUNT(*) As num_rows ... UNION ALL ...) AS Tbl1 to get the total. Or you can create two views and do the same thing.
Aug
27
answered How can I test sql scripts intended to run with sqlcmd -v in Sql Server Management Studio
Aug
27
answered Get and count all blog posts and all users that fits a LIKE condition (two sepparated queries)
Aug
27
awarded  Commentator
Aug
27
comment Performance tuning for gap analysis of table
Thanks to the CTE, it runs in 14 seconds with a few more indexes added. Thanks for all the help.
Aug
27
awarded  Scholar
Aug
27
awarded  Supporter
Aug
27
accepted Performance tuning for gap analysis of table
Aug
27
comment Performance tuning for gap analysis of table
Marking this as the answer. I still have a little ways to go, but the CTE is the right path. Thanks for the help, Edward and Rob.
Aug
27
comment Performance tuning for gap analysis of table
It filters it to a small amount for some of the time, but it can be set to up to 1 year, which will filter it down to about 2M records.
Aug
27
comment Performance tuning for gap analysis of table
Sorry, Edward. Should have been more clear. It's a "foreign key" which is copied from the pk of the parent table. This table is built on changes to the primary table just to make this report more speedy.
Aug
27
comment Performance tuning for gap analysis of table
I changed it around to have a CX only on the Id (with you and Edward's help) column and selected the min/max id's at first since the created date should be in the same order as the id's. It now returns at 58 seconds on my local, which is a big improvement on the CTE.
Aug
27
comment Performance tuning for gap analysis of table
Ok. I think I understand. I did switch the CreateDateUTC on the clustered to be first, but you are saying create it only on this, but it must be unique. Hmmmm. Maybe I should make a cluster on the existing ID column since it's unique and should be relatively ordered on insert?
Aug
27
comment Performance tuning for gap analysis of table
Not sure what you mean by particularly selective. It is fairly unique to the DeviceESN, but not guaranteed. Sometimes the devices have some strange things going on with the dates. Also, I think after looking at the execution plan, maybe indexed persisted columns for the minus one and plus one would help. I will take a look at joining by rowid. Also added this to the CTE: And (@Validator = '' Or @Validator = DeviceESN) And changed the other filter to be gated by the default first.
Aug
27
awarded  Autobiographer
Aug
27
comment Performance tuning for gap analysis of table
(154 row(s) affected) Table 'Worktable'. Scan count 2763996, logical reads 16693695, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'TapGapDetail'. Scan count 3, logical reads 61093, physical reads 1, read-ahead reads 1512, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected)