April 25, 2009 at 9:54 pm
How about this one? Fresh off the presses...
1. Create temporary table "A"
2. Populate it with base ids
3. Query table "B" and update one column in temp "A"
4. Query table "B" AGAIN and update another column in temp "A"
5. Repeat step 4 a couple more times for good measure.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 25, 2009 at 11:47 pm
Bob Hovious (4/25/2009)
How about this one? Fresh off the presses...1. Create temporary table "A"
2. Populate it with base ids
3. Query table "B" and update one column in temp "A"
4. Query table "B" AGAIN and update another column in temp "A"
5. Repeat step 4 a couple more times for good measure.
Sounds like some of the code my predecessor wrote. Doesn't make sense, why not load all the data you need from the table the first time.
April 25, 2009 at 11:55 pm
My thought exactly, Lynn. It may be a case of procedural addiction. No cursor or while loop, but still a sort of step-by-step approach.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 26, 2009 at 3:40 am
Bob Hovious (4/25/2009)
Hey Flo:I haven't seen anything. When, where, and how did you send it? 🙂
Hi Bob
Strange...? I'm sure that the bottle was closed as I put into water. 😛
I just asked if I would be allowed to use parts of your double-barreled tally in my article. Since now I'm not sure if I need it but if yes I want to be sure that it is okay for you. Naturally it would be published as your solution!
Greets
Flo
April 26, 2009 at 3:42 am
GilaMonster (4/25/2009)
Florian Reischl (4/25/2009)
The more I learn the more I konw what I do not knowPsst, Flo, there's typo in your sig.
Oops...
Thanks
Gail!
April 26, 2009 at 3:57 am
Bob Hovious (4/25/2009)
How about this one? Fresh off the presses...
And what about this:
Some colleagues of my company came to me and asked me to have a look to their dashboard and investigate why it performs so bad:
* There about 40 different numbers shown
* Every number is calculated by a own query (some are very alike and definitely could be calculated in one step)
* Sure, queries to non indexed columns
* Many "LEFT JOIN"s which could be handled as UNIONs (referenced ORs)
* And the best: There where no no aggregations! All queries executed a "SELECT *" for up to 10 huge tables. The returned data are only used to show the COUNT in dashboard. All data are completely thrown away after the query was executed.
Greets
Flo
April 26, 2009 at 7:03 am
Greetings Flo:
Never saw the bottle, but you're welcome to anything I ever post up here, as is everyone else. I wouldn't think you'd need my code after you RE-doubled it. 🙂
As for the dashboard, where big tables are concerned, even using
Edited to add:
I just realized that I posted code here !! :w00t: Thread forgive me !!
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 26, 2009 at 7:43 am
Bob Hovious (4/26/2009)
As for the dashboard, where big tables are concerned, even using
--
select count(*) from dbo.sometable as numberOfRows
--
is going to run slower than
--
declare @tableStats table
(name varchar(50), numberOfRows int, reserved varchar(30), data varchar(30)
,indexSize varchar(30), unused varchar(30)
)
insert into @tablestats
exec sp_spaceused 'dbo.sometable'
select numberOfRows from @tablestats
--
True, but especially on SQL 2000 and earlier, the values returned by sp_spaceused (or by querying sysindexes) were not guaranteed to be accurate. They might be, but they might not. I've seen cases back on SQL 2000 where count(*) returned a massively different value from sp_spaceused.
http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/
If all you want is a general idea of the row count, great. If you need exact values (or counts of rows satisfying various conditions), use count(*)
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
April 26, 2009 at 7:47 am
Bob Hovious (2/19/2009)
Personally though, I would rather have a smart, motivated person with very little knowledge but the ability and eagerness to learn, than a moderately knowledgeable person with no willingness to learn.
Amen.
Ignorance is merely the absence of knowledge on a particular subject.
Stupidity is an unwillingness to admit to ignorance and do something constructive about it.
Okay, so I'm still catching up on this thread, and this quote is from mid-Feb. But this brings back a story.
I used to have a guy renting a room from me. We were talking about something one night, and I could see he didn't know anything about the subject....
Me: "It's okay; you're just ignorant about that"
Him (indignant): "I'M NOT IGNORANT!. I just don't know what you're talking about"
Me:
(well, come on. What can you say nicely after a comment like that?)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 26, 2009 at 8:09 am
True enough, Gail. I'm guilty both of assuming that Flo is on 2005/2008 and that a dashboard application looking at huge files only needs a "close enough" value. If someone is looking at row counts of huge tables in volatile, high-transaction environments, the count has probably changed by the time they look at it.
I just remembered a bit from the old Beverly Hillbillies tv show, where Jethro Bodeen has a new watch and someone asks him what time it is. He looks at his watch and says
"It is exactly 1:35 and 26 seconds..... wait, 27 seconds.... 29 seconds..... "
😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 26, 2009 at 8:19 am
Okay, so I'm still catching up on this thread, and this quote is from mid-Feb.
Wayne:
You need to admit that you need help.
I'd like to invite you to a meeting of Threadaholics Anonymous.
If you need a sponsor, I'll be happy to volunteer.
With counseling, support, and perhaps appropriate medication, you can break this terrible addiction.
I'm pulling for you, man.
Sincerely,
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 26, 2009 at 8:19 am
Bob Hovious (4/26/2009)
True enough, Gail. I'm guilty both of assuming that Flo is on 2005/2008 and that a dashboard application looking at huge files only needs a "close enough" value. If someone is looking at row counts of huge tables in volatile, high-transaction environments, the count has probably changed by the time they look at it.
True, but try explaining that to some management types sometimes. It's ... entertaining.
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
April 26, 2009 at 8:35 am
I've done it many times.... and yes it is 😀
Mostly I've had to explain to someone why I favor running many reports against summary tables that are populated with totals built at the end of each business day. If a report looks at trends or makes comparisons from day to day, you don't have all the information to make the comparison until the day is over. So, there is usually no point to trying to include today's data, which will change dramatically from early morning to late afternoon. In the few cases where there is a legitimate reason to see up-to-the-minute numbers, it's faster to just build the totals for today, and compare them to the precalculated totals from yesterday's summary.
Analysis Services does this on a grand scale. I guess running a summary query and building an index or two just isn't sexy enough....
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 26, 2009 at 8:36 am
Hi Bob
Bob Hovious (4/26/2009)
Never saw the bottle, but you're welcome to anything I ever post up here, as is everyone else. I wouldn't think you'd need my code after you RE-doubled it. 🙂
Thanks!
As for the dashboard, where big tables are concerned, even using
...
Sorry, wrong explained. The dashboard is no system dashboard but an operational. It's a ERP system of an international freight forwarder. The dashboard shows shipment statistics and planning information, so the data are not only a "SELECT COUNT(*) FROM ...". There are criteria for the aggregations. The dashboard only shows the count of (e.g.) shipments but selects the complete rows (matching the criteria) and aggregates on client side.
It's already changed. I showed them the IO statistics and they understood the problem...
exec sp_spaceused 'dbo.sometable'
I didn't know sp_spaceused, but on SQL Server 2000 I usually just used the "rows" column of "sysindexes". I think it works equal.
... Avoided to write code to not displease "The Thread" *bow*
Greets
Flo
April 26, 2009 at 8:46 am
The rows column would be the same.
Do you think if I went back and edited my post to remove the code, the Thread might forgive me?
And... my assumption about the dashboard was wrong.... one day I will learn not to type while not completely caffeinated. 😛
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 3,676 through 3,690 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply