database errors: million

Showing posts with label million. Show all posts

Wednesday, March 7, 2012

crystal processing LARGE number of rows

i have a crystal report, oracle is pumping it out a million rows of data. regularly i would expect this report to have 50k-5mil rows of data sent to it from oracle. the 1 million rows oracle starts returning in sql*plus within half an hour from my stored procedure. i wonder if turning the stored proc into a pipelined one would help; answer i'm getting now is not really.

we have an online view of the report and an export to excel view. the online report is through the web with the crystal viewer. last run this report took 12 hours to complete. not sure how large it is (MB) - there are basically 3 types of pages created off the data; i believe these are called subreports. i am not a crystal developer, i did developer the proc that returns the million rows though.

the way the report works, is you see a list of top level entities (repeated multiple times in the data) - clicking on one of them takes you to a 2nd level entity (repeated) and clicking on one of these takes you to a 3rd page where all the (unique) products reside in that entity.

current dataset:
A z 1234
A z 2222
A y 3333
B m 5555

cr page 1:

A
B

user clicks A; page 2:

z
y

user clicks z; page 3:

1234
2222

of the 12 hours (only ran online/crystal viewer version) i expect the data to be returned in... 1 hour? not sure. majority of the time appears to be on crystal's part for processing though. does crystal start processing as soon as it tastes data, or does it need to wait for the whole result set? can i break up the pages into different procs (3) and still maintain links between them? please help, interesting in any and all suggestions. thanks!Cant you use three seperate reports by filtering the relevent data so that each report will have partial data?

Friday, February 24, 2012

Crossjoining large dimensions which are close to 1-to-1

We have two dimensions which are about 2 million members each. The fact table that joins those two dimensions is pretty sparse because really those two dimensions are highly related to each other. (They're not enough related to model as one dimension though, trust me.) So when you run the following, the count it returns is around 2.3 million:

with member test as

Exists(

[Dimesion1].[Attrib1].[Attrib1].Members

* [Dimension2].[Attrib2].[Attrib2].Members

, "My Fact Table"

).Count

select test on 0

from [My Cube]

The problem is that statement takes 5 minutes to execute and causes SSAS to consume 3GB of memory.

Any thoughts on improving this?

Could you describe the set you are attempting to generate with the [test] expression? Though valid, how you are using EXISTS is a little unusual.

Also, could you explain what you mean by the fact table being "sparse"?

Thanks,

Bryan

|||

Bryan-

Yea. A little background might help. We're trying to use the UDM in the true sense of the word... not just for summary level reports which take advantage of high degrees of aggregation, but also for more detail level reports. We're running into some problems using it for semi-detail level reports because of the issue mentioned in this thread.

While car rental isn't remotely the industry I'm working in, it will serve to illustrate the concept. Let's say we have a Driver dimension containing 3 million people and a VIN dimension containing 2 million cars. We take a daily snapshot of who has rented what car. On any given day, there's roughly a 1-to-1 correspondence between drivers and VINs. But a particular car is rented by different drivers over time. So when I say the fact table is "sparse" I mean that it doesn't contain 4,000,000,000,000 rows per day (which would be 2 million X 2 million).

One obvious answer to this question is to use the drillthrough MDX statement. We're looking into rearchitecting the app to do that, but there are a couple of challenges... one being that there are a couple of other dimensions in that fact table which explode out the rows by 5-10x per VIN... but in my query, I only want to see one row per VIN, which is only a small degree of summarization.

So what I'm looking for is to see if anybody else has experience crossjoining large dimensions and making it work. Are there functions I should look into? Just looking for suggestions to try.

|||

Is there any difference in performance with his query? (It's probably the same but I have to ask.)

Also, do you have an aggregation built on [Attrib1] + [Attrib2] ?

Code Snippet

with member [Measures].[Combo Count] as
COUNT(
EXISTS(
[Dimension1].[Attrib1].[Attrib1].Members,
[Dimension2].[Attrib2].[Attrib2].Members,
'My Fact Table'
)
)
select
[Measures].[Combo Count] on 0
from [My Cube]

|||

It wasn't clear from my original question, but I'm actually wanting to select those two attributes on rows:

select {} on 0,

Exists(

[Dimesion1].[Attrib1].[Attrib1].Members

* [Dimension2].[Attrib2].[Attrib2].Members

, "My Fact Table"

) on 1

from [My Cube]

So I need both in the first parameter of exists.

|||

Understood. Try running that sample anyway and let's see how that performs. It will perform a similar evaluation the two attributes. If this performs well, then we can start to move forward to see exactly when we introduce the performance problem. That should give us a clue as to how we might address this.

Also, did you have that aggregation set up and populated in your cube?

Thanks,
Bryan

|||

I am not sure how trying Bryan's suggestion will help here since it returns different result.

I am puzzled, however, why Exists with measure group takes 5 minutes and 3 GBs on only 2.3 million records, but drillthrough works fast. After all both drillthrough and Exists with measure group go through pretty much the same codepath.

Have you played with DISCOVER_MEMORY to see where these 3 GBs go ? My math goes like that: Since you run your query on attribute hierarchies, there should be no more than 8 bytes per member, i.e. 16 bytes per tuple maximum. Therefore, 2.3 million*16 bytes = about 36 MBs - 100 times less than what you observed.

|||

To explain my approach....

The way EXISTS is being used in the orginal query strikes me as unusual. We normally define two sets and evaluate across a measure group (if needed). I'm wanting to see how the engine evaluates [Attrib1] vs [Attrib2] using his syntax vs. a more traditional syntax. This may lead no where, but we have seen situations where variations in syntax, though producing the same result, take widely different paths. Though this doesn't return the same result, its a starting point.

The following statement may also be provide a test, closer to where Gregg is wanting to go. Bottomline, I'm trying to see if we can find a mechanism that evaluates Attrib 1 against Attrib 2 that returns quicker.

SELECT [Measures].[My Measure] on 0, NON EMPTY [Dim 1].[Attrib 1].[Attrib 1].Members * [Dim 2].[Attrib 2].[Attrib 2].Members on 1

FROM [Adventure Works]

Regarding the count, I'm wanting to remove the cost of returning a large cellset from our evaluation.

Good luck,
B.

|||

Bryan, I don't think NON EMPTY is the way to go. I don't need to retrieve a measure value, just the valid combinations. Performance using the NonEmpty function was just about the same as Exists, interestingly enough.

As for your comment about aggs, I dismissed it initially thinking it wasn't relevant for two reasons. Both I have disproven both with some tests:

1. I initially thought that those dimensions are way too high cardinality to be involved in aggs. That's a sensible assumption, but I decided to test it. I built an agg on Date, Dim1.Attrib1, and Dim2.Attrib2. That agg turned out to be about 7% of the size of the fact.data file. Now that I think about it, that makes sense. Using that granularity of agg shrinks the distinct rowcount by 5x. And excluding 6 of 9 dimension keys shrinks the size of a row by 3x. So (100/5)/3 = 6.6%. So the takeaway is that you shouldn't assume an agg is too big out of hand.

2. I had just assumed that Exists-with-a-measure-group couldn't run off an agg. Turns out it can!!! (Was this what you were hinting at Bryan?) I used filemon to determine that it was hitting only the agg file on disk, not the fact.data file. In one test I did the Exists query ran 2x faster and produced about 10x less IO. I'll add this to the list of topics to blog out when I get time :-)

Mosha, you're omniscient as always. I'm having trouble reproducing the high memory usage today with the Exists function. The memory usage is just about in line with your guesstimate. It spends most of its time within Serialize Results as you would expect for having large sets on the axes. It's still slower than I would hope, but your statements give me the impression that there's not really a better way to write the MDX, so that pretty much answers my question and closes off this thread. As mentioned above, I think looking into partitioning and aggs is the way to go instead of changing the MDX.

I think I must have been a bit tired Thursday when I was starting this thread because it appears I got memory consumption backwards between Exists-with-a-measure-group and drillthrough. It's drillthrough that uses such a huge amount of memory. It actually consumes over 5GB of memory. The high memory usage is due to setting maxrows to 10,000,000. I'm still surprised at how much memory it uses, but oh well, I guess I won't look at using drillthrough cause it doesn't perform as well as I had anticipated. I reported this as a bug mostly because I saw filemon saying the drillthrough command hit each segment in the fact.data file twice: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=282134

Thanks y'all.

Tuesday, February 14, 2012

cross database insert

I have an application inserting, deleteing and changing records at a high
volume. Normally I get about 3 million of my transaction a second. For
each transaction I add a log entry to a table. My problem is that I wanted
to create a second database to store the log entries. Once I changed the
log insert to use the second database it cuts my transaction count down to
about 400K an hour. So is doing an insert to a second database always going
to be slow? Is there anything that can be done to improve performance? The
insert is done in a stored procedure in the first database.
BobI can imagine two reasons why the cross database work makes it slower:
You do it all in one transaction, and going across databases makes the transaction implemented
internally using a 2-phase commit protocol (more expensive than if inside one database).
You are pushing the log writes so that you end up with waiting for physical writes, and if you have
the transaction log files for the two databases on the same physical disk(s), you end up waiting for
head-movement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bob" <msgdev@.hotmail.com> wrote in message news:%23wv9my$VGHA.5668@.TK2MSFTNGP15.phx.gbl...
>I have an application inserting, deleteing and changing records at a high volume. Normally I get
>about 3 million of my transaction a second. For each transaction I add a log entry to a table. My
>problem is that I wanted to create a second database to store the log entries. Once I changed the
>log insert to use the second database it cuts my transaction count down to about 400K an hour. So
>is doing an insert to a second database always going to be slow? Is there anything that can be
>done to improve performance? The insert is done in a stored procedure in the first database.
>
> Bob
>|||The only reason I am doing this is because of the size limitation of
SQLExpress. I need more space for my logs. Anyone have any suggestion on
another work around beside upgrading to the full version of SQL server?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OPUNT5$VGHA.5076@.TK2MSFTNGP14.phx.gbl...
>I can imagine two reasons why the cross database work makes it slower:
> You do it all in one transaction, and going across databases makes the
> transaction implemented internally using a 2-phase commit protocol (more
> expensive than if inside one database).
> You are pushing the log writes so that you end up with waiting for
> physical writes, and if you have the transaction log files for the two
> databases on the same physical disk(s), you end up waiting for
> head-movement.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Bob" <msgdev@.hotmail.com> wrote in message
> news:%23wv9my$VGHA.5668@.TK2MSFTNGP15.phx.gbl...
>>I have an application inserting, deleteing and changing records at a high
>>volume. Normally I get about 3 million of my transaction a second. For
>>each transaction I add a log entry to a table. My problem is that I
>>wanted to create a second database to store the log entries. Once I
>>changed the log insert to use the second database it cuts my transaction
>>count down to about 400K an hour. So is doing an insert to a second
>>database always going to be slow? Is there anything that can be done to
>>improve performance? The insert is done in a stored procedure in the
>>first database.
>>
>> Bob
>|||The cross database insert is always going to do a distributed transaction
and distributed commits get serialized so it's going to be slow. The people
around here that do this have several empty databases created and when they
get above a certain size, they change the database name for their inserts.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <msgdev@.hotmail.com> wrote in message
news:eB2PzCAWGHA.5036@.TK2MSFTNGP15.phx.gbl...
> The only reason I am doing this is because of the size limitation of
> SQLExpress. I need more space for my logs. Anyone have any suggestion on
> another work around beside upgrading to the full version of SQL server?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OPUNT5$VGHA.5076@.TK2MSFTNGP14.phx.gbl...
>>I can imagine two reasons why the cross database work makes it slower:
>> You do it all in one transaction, and going across databases makes the
>> transaction implemented internally using a 2-phase commit protocol (more
>> expensive than if inside one database).
>> You are pushing the log writes so that you end up with waiting for
>> physical writes, and if you have the transaction log files for the two
>> databases on the same physical disk(s), you end up waiting for
>> head-movement.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Bob" <msgdev@.hotmail.com> wrote in message
>> news:%23wv9my$VGHA.5668@.TK2MSFTNGP15.phx.gbl...
>>I have an application inserting, deleteing and changing records at a high
>>volume. Normally I get about 3 million of my transaction a second. For
>>each transaction I add a log entry to a table. My problem is that I
>>wanted to create a second database to store the log entries. Once I
>>changed the log insert to use the second database it cuts my transaction
>>count down to about 400K an hour. So is doing an insert to a second
>>database always going to be slow? Is there anything that can be done to
>>improve performance? The insert is done in a stored procedure in the
>>first database.
>>
>> Bob
>>
>|||Thats going to be hard in my situation. I have serveral processes adding
records to a table. A second process doing a select on that table then
passing each record to a worker thread. The thread process the data and
then deletes the original record from the table. After each event the
status is logged to another table. This logging is what is causing my
problem. It would be hard to switch tables since I could have about 100k
items queued to be processed at any time.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23bq9IHAWGHA.2360@.TK2MSFTNGP09.phx.gbl...
> The cross database insert is always going to do a distributed transaction
> and distributed commits get serialized so it's going to be slow. The
> people around here that do this have several empty databases created and
> when they get above a certain size, they change the database name for
> their inserts.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Bob" <msgdev@.hotmail.com> wrote in message
> news:eB2PzCAWGHA.5036@.TK2MSFTNGP15.phx.gbl...
>> The only reason I am doing this is because of the size limitation of
>> SQLExpress. I need more space for my logs. Anyone have any suggestion
>> on another work around beside upgrading to the full version of SQL
>> server?
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OPUNT5$VGHA.5076@.TK2MSFTNGP14.phx.gbl...
>>I can imagine two reasons why the cross database work makes it slower:
>> You do it all in one transaction, and going across databases makes the
>> transaction implemented internally using a 2-phase commit protocol (more
>> expensive than if inside one database).
>> You are pushing the log writes so that you end up with waiting for
>> physical writes, and if you have the transaction log files for the two
>> databases on the same physical disk(s), you end up waiting for
>> head-movement.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Bob" <msgdev@.hotmail.com> wrote in message
>> news:%23wv9my$VGHA.5668@.TK2MSFTNGP15.phx.gbl...
>>I have an application inserting, deleteing and changing records at a
>>high volume. Normally I get about 3 million of my transaction a second.
>>For each transaction I add a log entry to a table. My problem is that I
>>wanted to create a second database to store the log entries. Once I
>>changed the log insert to use the second database it cuts my transaction
>>count down to about 400K an hour. So is doing an insert to a second
>>database always going to be slow? Is there anything that can be done to
>>improve performance? The insert is done in a stored procedure in the
>>first database.
>>
>> Bob
>>
>>
>|||How critical is losing a log record? You could try logging in a different
transaction or even logging to a file if you ca afford to lose a log record
when the system loses power.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <msgdev@.hotmail.com> wrote in message
news:O$dwuaAWGHA.4360@.TK2MSFTNGP14.phx.gbl...
> Thats going to be hard in my situation. I have serveral processes adding
> records to a table. A second process doing a select on that table then
> passing each record to a worker thread. The thread process the data and
> then deletes the original record from the table. After each event the
> status is logged to another table. This logging is what is causing my
> problem. It would be hard to switch tables since I could have about 100k
> items queued to be processed at any time.
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23bq9IHAWGHA.2360@.TK2MSFTNGP09.phx.gbl...
>> The cross database insert is always going to do a distributed transaction
>> and distributed commits get serialized so it's going to be slow. The
>> people around here that do this have several empty databases created and
>> when they get above a certain size, they change the database name for
>> their inserts.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Bob" <msgdev@.hotmail.com> wrote in message
>> news:eB2PzCAWGHA.5036@.TK2MSFTNGP15.phx.gbl...
>> The only reason I am doing this is because of the size limitation of
>> SQLExpress. I need more space for my logs. Anyone have any suggestion
>> on another work around beside upgrading to the full version of SQL
>> server?
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OPUNT5$VGHA.5076@.TK2MSFTNGP14.phx.gbl...
>>I can imagine two reasons why the cross database work makes it slower:
>> You do it all in one transaction, and going across databases makes the
>> transaction implemented internally using a 2-phase commit protocol
>> (more expensive than if inside one database).
>> You are pushing the log writes so that you end up with waiting for
>> physical writes, and if you have the transaction log files for the two
>> databases on the same physical disk(s), you end up waiting for
>> head-movement.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Bob" <msgdev@.hotmail.com> wrote in message
>> news:%23wv9my$VGHA.5668@.TK2MSFTNGP15.phx.gbl...
>>I have an application inserting, deleteing and changing records at a
>>high volume. Normally I get about 3 million of my transaction a
>>second. For each transaction I add a log entry to a table. My problem
>>is that I wanted to create a second database to store the log entries.
>>Once I changed the log insert to use the second database it cuts my
>>transaction count down to about 400K an hour. So is doing an insert to
>>a second database always going to be slow? Is there anything that can
>>be done to improve performance? The insert is done in a stored
>>procedure in the first database.
>>
>> Bob
>>
>>
>>
>

Wednesday, March 7, 2012

crystal processing LARGE number of rows

Friday, February 24, 2012

Crossjoining large dimensions which are close to 1-to-1

Tuesday, February 14, 2012

cross database insert

cross database insert

cross database insert

database errors

Blog Archive

About Me