November 1, 2010 at 11:31 am
I was torn about whether to post this question here or in the forums, but settled on the forums because it might help other people too. Still, it is a fairly casual question so I thought I'd mention it here as well
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 1, 2010 at 11:33 am
CirquedeSQLeil (11/1/2010)
In case anybody is interested...Steve is Presenting to my UG this Thursday. Meeting starts at 6:30 Pacific Nov 4th.
https://www.livemeeting.com/cc/usergroups/join?id=PPBZ4S&role=attend
Please use meeting ID: PPBZ4S
Excellent, thanks!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 1, 2010 at 11:33 am
Alvin Ramard (11/1/2010)
Gail, now that you have the extra bandwidth, you should be able to do the next 20k in half the time, right? 😛(Just kidding)
No, that just means she'll be able to answer OUR questions faster...
😀
Congratulations Gail!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 1, 2010 at 11:49 am
Alvin Ramard (11/1/2010)
Gail, now that you have the extra bandwidth, you should be able to do the next 20k in half the time, right?
Unfortunately they didn't double my bandwidth, just added 20%, and they didn't add 20% to the length of the day.
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 1, 2010 at 12:21 pm
jcrawf02 (10/28/2010)
trying to figure out what I *should* be doing to be most efficient when I want to go digging through some of our data. Don’t know if there are cubes that already exist that I can browse through Excel (as a front-end), or if I should think about creating local SSAS cubes on my machine when I want to go digging (seems like that might be overkill)Currently trying to extract and analyze about 115k records for emergency room claims in CY2009, which Excel 2007 can hold. However, each record has about 17 fields, which means when I try to pivot that data, I am manipulating almost 2million data fields, and Excel stops working. (assuming the number of fields is why, no formulas involved at all)
I suppose I could look to Access, but I really don’t like Access, so wondering if I can jump up a level and either do what I’m trying to do directly from the DW cubes (which may not have all the elements I need, and I don’t want to go starting a project with IT to fix that, I’m just fishing for data at this point), or potentially create my own cubes in SSAS, which is installed on my box, and run them locally to go digging.
I guess my other option is to create a ton of little pivots, each looking at one or two elements, definitely not a time-saver. Alternately, I just play with it in SQL, without all the pretty colors and pi-charts
Any advice appreciated.
Thanks,
Jon
I ran my test with 136,000 rows, 17 columns of data, using a couple of measures, grouping by dates.
Sub second response times.
Although I am using Office 2010, x64 bit W7 machine, with 8 GB of RAM.
So my version of office is newer, and my workstation likely has a bit more RAM. But I am only on 32 bit office, and only a core 2 duo processor.
I have ugly visions of what our users will do with this.
But it does show some very ad hoc capability we may have to deal with.
And the end document was only a little over 13 megs, which might be a comparision to 2007 for some of the optimization that may be part of this platform.
The only thing I was noticing - I couldn't just paste 1000's of rows multiple times to expand my data. It seemed to get maybe 20k for a couple of pastes, then needed a save and close.
And I did start out with a smaller named range, and a simple pivot table, expanding the named range and refreshing as I coppied rows.
From what I saw, if you have Office 2010 and a machine with 4 GB available, this might not be so bad.
No install of 2007 to test here, but if you'd like I could bring a copy home and test on 2007 (x64 bit, 4 GB RAM) and see how much it slows down.
Greg E
November 1, 2010 at 12:47 pm
Thanks for the tests Greg, no need to play with 2007. You bring up a good point, because I won't be the only one here at work who tries to do this, and I'm sure once people get this idea in their heads they'll be trying this and then calling IT to find out why it won't work.
(FWIW - I'm on XP SP3, Core 2 duo, 32 bit Office with only 1.95 GB RAM, and I have twice the RAM of the average bear around here)
Oh well, guess I'll go eat my elephant in small bites for the moment. 😉
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 1, 2010 at 1:41 pm
CirquedeSQLeil (11/1/2010)
In case anybody is interested...Steve is Presenting to my UG this Thursday. Meeting starts at 6:30 Pacific Nov 4th.
https://www.livemeeting.com/cc/usergroups/join?id=PPBZ4S&role=attend
Please use meeting ID: PPBZ4S
Thanks Jason - it's been added to my calendar.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 1, 2010 at 1:48 pm
Stefan Krzywicki (11/1/2010)
I was torn about whether to post this question here or in the forums, but settled on the forums because it might help other people too. Still, it is a fairly casual question so I thought I'd mention it here as well
I just gave you a reply I bet you didn't consider.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 1, 2010 at 1:55 pm
WayneS (11/1/2010)
Stefan Krzywicki (11/1/2010)
I was torn about whether to post this question here or in the forums, but settled on the forums because it might help other people too. Still, it is a fairly casual question so I thought I'd mention it here as wellI just gave you a reply I bet you didn't consider.
That would be a good one to test to see if it works as you said. Your logic seems sound to me.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 1, 2010 at 2:30 pm
WayneS (11/1/2010)
Stefan Krzywicki (11/1/2010)
I was torn about whether to post this question here or in the forums, but settled on the forums because it might help other people too. Still, it is a fairly casual question so I thought I'd mention it here as wellI just gave you a reply I bet you didn't consider.
You are correct, sir!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 1, 2010 at 2:55 pm
jcrawf02 (11/1/2010)
Thanks for the tests Greg, no need to play with 2007. You bring up a good point, because I won't be the only one here at work who tries to do this, and I'm sure once people get this idea in their heads they'll be trying this and then calling IT to find out why it won't work.(FWIW - I'm on XP SP3, Core 2 duo, 32 bit Office with only 1.95 GB RAM, and I have twice the RAM of the average bear around here)
Oh well, guess I'll go eat my elephant in small bites for the moment. 😉
My machine was spec'd out fo ra CAD workstation. Next best thing to a server.:-)
I still think browsing what cubes they have, and seeing what columns you have - dimensions and measure candidates - you'd get some idea what might be missing. I can't imagine that you don't have some idea of what you'd be looking for.
And there may be some natural breaks in the data, like Regions for example, that would allow for pivot table in 2007 against a subset of the data you have.
Greg E
November 1, 2010 at 4:19 pm
CirquedeSQLeil (11/1/2010)
In case anybody is interested...Steve is Presenting to my UG this Thursday. Meeting starts at 6:30 Pacific Nov 4th.
https://www.livemeeting.com/cc/usergroups/join?id=PPBZ4S&role=attend
Please use meeting ID: PPBZ4S
Thanks for the heads up .. put it on my calendar
November 1, 2010 at 4:32 pm
Thanks Wayne and Ron and Jon - we'll hopefully see you at the meeting.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 1, 2010 at 5:39 pm
Can someone swing through here and see if they can understand what he's looking for better than I can? Or can maybe phrase what we need in a way he'll understand better?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2010 at 5:46 pm
Greg Edwards-268690 (11/1/2010)
jcrawf02 (11/1/2010)
Thanks for the tests Greg, no need to play with 2007. You bring up a good point, because I won't be the only one here at work who tries to do this, and I'm sure once people get this idea in their heads they'll be trying this and then calling IT to find out why it won't work.(FWIW - I'm on XP SP3, Core 2 duo, 32 bit Office with only 1.95 GB RAM, and I have twice the RAM of the average bear around here)
Oh well, guess I'll go eat my elephant in small bites for the moment. 😉
My machine was spec'd out fo ra CAD workstation. Next best thing to a server.:-)
I still think browsing what cubes they have, and seeing what columns you have - dimensions and measure candidates - you'd get some idea what might be missing. I can't imagine that you don't have some idea of what you'd be looking for.
And there may be some natural breaks in the data, like Regions for example, that would allow for pivot table in 2007 against a subset of the data you have.
Greg E
Well, that's kinda the point. I don't know what I don't know yet. It would be nice to have a way to dig through the data by pulling out some elements that I know I'll use detail on if I find something, then mess with those on a aggregate level to see what I can find. For instance, one of the focus studies at work changed direction some, and I thought it might be a bad idea, but until they came up with the final focus list of patients, I wasn't able to really show them WHY it was a bad idea. Now we're stuck in a place where we asked patients to work with us in a study, and yet that study as designed won't accomplish what it is set out to, because we're looking at the wrong set of patients.
I suppose that knowing all the elements I'd want to see at the detail level, I should just request IT to have those present in their cube, but I have a hunch that they'll tell me it'll take a year to get them there. And a BRD. And Project approval from on high, because it wasn't in their project list originally, nor was it in the list under review for 2011.
As for the subsets of data, that's true, we have regions/counties/age groups/etc, but I'm not even sure what I'll want to go looking for at this point, and those things may cross those boundaries.
growing pains for me, I've not had the chance to ask fun questions before, always been stuck running the next report, so this is a good thing really. Latest bit of fun was comparing the NYU ED Algorithm against our patient data and seeing how relevant it really is. (hint - not so much, when it's adopted wholesale and not adjusted to fit)
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 15 posts - 21,061 through 21,075 (of 66,749 total)
You must be logged in to reply to this topic. Login to reply