Hi
I have a query which I would like to optimize and thought I had found a
solution.
The query is a join of several selects from several different tables.
So we have in general the following:
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
JOIN
SELECT table1.Col1 FROM table2 WHERE exp2
ON table1.Col1 = table2.Col1
)
or something like that. Of course my query was a lot larger than this,
but it is just to illustrate the problem.
The above was too slow, and I made an attempt to optimize it and
thought it did work. The following is what I did:
Instead of the SELECT's I could create temporary tables and select into
those so I get the following:
SELECT table1.Col1
INTO #temp_table1
FROM table1 WHERE exp1
SELECT table1.Col1
INTO #temp_table2
FROM table2 WHERE exp2
And then join those two tables:
SELECT Col1 FROM
(
#temp_table1
JOIN
#temp_table2
ON #temp_table1.Col1 = #temp_table2.Col1
)
and at the end drop the temporary tables.
DROP Table #temp_table1
DROP Table #temp_table2
This gives the same result as the original statement.
I profiled a bit and saw that if I created the temp tables, and ran the
join statement several times after each other, the first time it is run
it take a lot of time compared to the following. Can anyone explain to
me why this is the case? I thought that the temporary tables were
created and loaded into the cache, and therefore all calls to the big
JOIN statement should take equally long time.
The thing is that I cannot reuse the temporary tables, so I get no
benefit from all the above.
Thank you very much in advance
JeejiHi
First of all the first script throws a syntax error. Is that MySQL syntax?
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 JOIN table2 ON table1.Col1 =table2.Col1 WHERE exp1 AND ....
) AS Der
--Or
SELECT * FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
UNION ALL
SELECT table1.Col1 FROM table2 WHERE exp2
) AS Der
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Uri,
I think that is just a small fragment of the query. (At least I hope it's
just a fragment...)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Hi.
I thought I made it clear that it was just a fragment of the code. But
now something new came to my attention.
I can see that if I clear the SQL server cache and call the first SQL
statement (the one without the temporary table), the SQL profiler shows
that this call takes longer (around twice) than it takes if I clear the
server cache and run the temporary table version of the call.
However if I do the same calls from a C# application, the opposite
occurs.
To clear the SQL server cache I use:
DBCC FreeSystemCache('All')
And for the C# application I am using .Net 2.0, using the
System.Data.SQLClient calls.
Any advice would just be great.
Arnie Rowland wrote:
> Uri,
> I think that is just a small fragment of the query. (At least I hope it's
> just a fragment...)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "jeeji" <jihad_dk@.yahoo.com> wrote in message
> news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> > Hi
> >
> > I have a query which I would like to optimize and thought I had found a
> > solution.
> >
> > The query is a join of several selects from several different tables.
> >
> > So we have in general the following:
> >
> > SELECT Col1 FROM
> > (
> > SELECT table1.Col1 FROM table1 WHERE exp1
> > JOIN
> > SELECT table1.Col1 FROM table2 WHERE exp2
> > ON table1.Col1 = table2.Col1
> > )
> >
> > or something like that. Of course my query was a lot larger than this,
> > but it is just to illustrate the problem.
> >
> > The above was too slow, and I made an attempt to optimize it and
> > thought it did work. The following is what I did:
> > Instead of the SELECT's I could create temporary tables and select into
> > those so I get the following:
> >
> > SELECT table1.Col1
> > INTO #temp_table1
> > FROM table1 WHERE exp1
> >
> > SELECT table1.Col1
> > INTO #temp_table2
> > FROM table2 WHERE exp2
> >
> > And then join those two tables:
> >
> > SELECT Col1 FROM
> > (
> > #temp_table1
> > JOIN
> > #temp_table2
> > ON #temp_table1.Col1 = #temp_table2.Col1
> > )
> >
> > and at the end drop the temporary tables.
> >
> > DROP Table #temp_table1
> > DROP Table #temp_table2
> >
> > This gives the same result as the original statement.
> >
> > I profiled a bit and saw that if I created the temp tables, and ran the
> > join statement several times after each other, the first time it is run
> > it take a lot of time compared to the following. Can anyone explain to
> > me why this is the case? I thought that the temporary tables were
> > created and loaded into the cache, and therefore all calls to the big
> > JOIN statement should take equally long time.
> > The thing is that I cannot reuse the temporary tables, so I get no
> > benefit from all the above.
> >
> > Thank you very much in advance
> > Jeeji
> >|||Sorry for the distrubance guys. I figured it out myself.
The problem is that I did not clear the cache from the C# application.
If I did, the same results show.
Jeeji
jeeji skrev:
> Hi.
> I thought I made it clear that it was just a fragment of the code. But
> now something new came to my attention.
> I can see that if I clear the SQL server cache and call the first SQL
> statement (the one without the temporary table), the SQL profiler shows
> that this call takes longer (around twice) than it takes if I clear the
> server cache and run the temporary table version of the call.
> However if I do the same calls from a C# application, the opposite
> occurs.
> To clear the SQL server cache I use:
> DBCC FreeSystemCache('All')
> And for the C# application I am using .Net 2.0, using the
> System.Data.SQLClient calls.
> Any advice would just be great.
> Arnie Rowland wrote:
> > Uri,
> >
> > I think that is just a small fragment of the query. (At least I hope it's
> > just a fragment...)
> >
> > --
> > Arnie Rowland, YACE*
> > "To be successful, your heart must accompany your knowledge."
> >
> > *Yet Another Certification Exam
> >
> >
> > "jeeji" <jihad_dk@.yahoo.com> wrote in message
> > news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> > > Hi
> > >
> > > I have a query which I would like to optimize and thought I had found a
> > > solution.
> > >
> > > The query is a join of several selects from several different tables.
> > >
> > > So we have in general the following:
> > >
> > > SELECT Col1 FROM
> > > (
> > > SELECT table1.Col1 FROM table1 WHERE exp1
> > > JOIN
> > > SELECT table1.Col1 FROM table2 WHERE exp2
> > > ON table1.Col1 = table2.Col1
> > > )
> > >
> > > or something like that. Of course my query was a lot larger than this,
> > > but it is just to illustrate the problem.
> > >
> > > The above was too slow, and I made an attempt to optimize it and
> > > thought it did work. The following is what I did:
> > > Instead of the SELECT's I could create temporary tables and select into
> > > those so I get the following:
> > >
> > > SELECT table1.Col1
> > > INTO #temp_table1
> > > FROM table1 WHERE exp1
> > >
> > > SELECT table1.Col1
> > > INTO #temp_table2
> > > FROM table2 WHERE exp2
> > >
> > > And then join those two tables:
> > >
> > > SELECT Col1 FROM
> > > (
> > > #temp_table1
> > > JOIN
> > > #temp_table2
> > > ON #temp_table1.Col1 = #temp_table2.Col1
> > > )
> > >
> > > and at the end drop the temporary tables.
> > >
> > > DROP Table #temp_table1
> > > DROP Table #temp_table2
> > >
> > > This gives the same result as the original statement.
> > >
> > > I profiled a bit and saw that if I created the temp tables, and ran the
> > > join statement several times after each other, the first time it is run
> > > it take a lot of time compared to the following. Can anyone explain to
> > > me why this is the case? I thought that the temporary tables were
> > > created and loaded into the cache, and therefore all calls to the big
> > > JOIN statement should take equally long time.
> > > The thing is that I cannot reuse the temporary tables, so I get no
> > > benefit from all the above.
> > >
> > > Thank you very much in advance
> > > Jeeji
> > >sql
Showing posts with label solution. Show all posts
Showing posts with label solution. Show all posts
Wednesday, March 28, 2012
Monday, March 12, 2012
Optimal solution for getting timestamp of last entry
Hi
I'm trying to find the optimal way of getting the timestamp of the last updated entry in an mssql database. A database is updated only about 5 times a minute, how ever a request for the time of the last entry could be around 1 per second. For this reason i was thinking of having a separate table which has a single row which is updated everytime a new entry is updated in the main table. I would then only need a simple SELECT statement and need very little processing power.
Is this the best method, or can you think of any others i could use?
many thanksWhat's the scope? Database wide or table specific?|||single table, single row? a potential bottleneck, but only 5/minute should be no problem, so yeah, that would be the simplest solution
but if this is just for your main table, consider an index on the datetime column descending, so that TOP 1 gets your last update easily|||but if this is just for your main table, consider an index on the datetime column descending, so that TOP 1 gets your last update easilyHow does SQL Server handle a monotonically decreasing index? Presumably with regular page splits? Like you say low activity will mitigate this somewhat.|||oh dear, i'm in over my head here
what is monotonically? no music? and how is monotonically different from sequentially? from consecutively?
and what's a page split?
sheesh, i should stick to stuff i know|||SELECT MAX(upd_dt) FROM (
SELECT MAX(upd_dt) AS upd_dt FROM tbl1 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl2 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl3 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl4 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl5 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl6) AS XXX
???????????????
You gotta give us more to go on|||Hi, its a pretty simple database, there are only two tables, one with all the data, and the other which will hold the single row with the latest time stamp.
I realise i can SELECT the top1 descending record from the main table, but won't that be more work than simply selecting a single value?
...or is selecting top1 more viable than loosing out on 'bottle necking' when selecting a single row..!|||more work? for you or for the server?
i'll bet if you timed the cpu cycles, it'd be pretty close|||more work? for you or for the server?
i'll bet if you timed the cpu cycles, it'd be pretty close
lol - i don't mind doing the work! - i just want this to be as optimal as possible...|||How many rows in the table?|||oh dear, i'm in over my head here
...
sheesh, i should stick to stuff i knowI don't think so - I think you just sometimes pretend you know less than you do so no one asks you anything remotely resembling a dba question :)|||In the main table, there will be approximately 1000 new rows per day.
I suppose this leads onto another question, are there any limits other than disk space, eg on the cpu that i should be aware of?|||Sorry I meant in total|||well theres zero rows at the moment because im in the middle of building the application and setting up the database. But i am expecting approx 1000 new rows a day - not sure what limits are advisable as to how many rows before i need to do something, eg create a new table or whatever?|||well theres zero rows at the moment because im in the middle of building the application and setting up the database. But i am expecting approx 1000 new rows a day - not sure what limits are advisable as to how many rows before i need to do something, eg create a new table or whatever?You won't get close to the sort of volume where you need to start getting canny at 1000 inserts a day.
Agreed with Rudy - just put the column in your table and then select MAX() of that column to get the last update. Index the column. I think I would index ASC but there you go. Triger to update it.
BTW - one table - is it normalised?|||ok - sounds good to me - can i just write this out how i see it working from what you've said above:
1) Index time/date column Ascending
2) When getting the last update, use something similiar to:
SELECT MAX(timeDateStampColumn) as "LastEntry"
FROM table;
BTW - one table - is it normalised?
My application is based around sms text messages, each new row in my table is basically a new text message, there are only a few columns (id, message, mobile number, dateTimeStamp) so no need to normalise. I'm at the designing stages of the project so will certainly be more tables added later on.|||My application is based around sms text messages, each new row in my table is basically a new text message, there are only a few columns (id, message, mobile number, dateTimeStamp) so no need to normalise. That table structure sounds fine although I would disagree and say that the very early stages are absolutely the right time to normalise. I would also strongly recommend you design your complete database before worrying about requirements like "how do I find the time of the last SMS?".|||point taken ;)
thanks everyone for your excellent help (as usual) :D
I'm trying to find the optimal way of getting the timestamp of the last updated entry in an mssql database. A database is updated only about 5 times a minute, how ever a request for the time of the last entry could be around 1 per second. For this reason i was thinking of having a separate table which has a single row which is updated everytime a new entry is updated in the main table. I would then only need a simple SELECT statement and need very little processing power.
Is this the best method, or can you think of any others i could use?
many thanksWhat's the scope? Database wide or table specific?|||single table, single row? a potential bottleneck, but only 5/minute should be no problem, so yeah, that would be the simplest solution
but if this is just for your main table, consider an index on the datetime column descending, so that TOP 1 gets your last update easily|||but if this is just for your main table, consider an index on the datetime column descending, so that TOP 1 gets your last update easilyHow does SQL Server handle a monotonically decreasing index? Presumably with regular page splits? Like you say low activity will mitigate this somewhat.|||oh dear, i'm in over my head here
what is monotonically? no music? and how is monotonically different from sequentially? from consecutively?
and what's a page split?
sheesh, i should stick to stuff i know|||SELECT MAX(upd_dt) FROM (
SELECT MAX(upd_dt) AS upd_dt FROM tbl1 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl2 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl3 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl4 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl5 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl6) AS XXX
???????????????
You gotta give us more to go on|||Hi, its a pretty simple database, there are only two tables, one with all the data, and the other which will hold the single row with the latest time stamp.
I realise i can SELECT the top1 descending record from the main table, but won't that be more work than simply selecting a single value?
...or is selecting top1 more viable than loosing out on 'bottle necking' when selecting a single row..!|||more work? for you or for the server?
i'll bet if you timed the cpu cycles, it'd be pretty close|||more work? for you or for the server?
i'll bet if you timed the cpu cycles, it'd be pretty close
lol - i don't mind doing the work! - i just want this to be as optimal as possible...|||How many rows in the table?|||oh dear, i'm in over my head here
...
sheesh, i should stick to stuff i knowI don't think so - I think you just sometimes pretend you know less than you do so no one asks you anything remotely resembling a dba question :)|||In the main table, there will be approximately 1000 new rows per day.
I suppose this leads onto another question, are there any limits other than disk space, eg on the cpu that i should be aware of?|||Sorry I meant in total|||well theres zero rows at the moment because im in the middle of building the application and setting up the database. But i am expecting approx 1000 new rows a day - not sure what limits are advisable as to how many rows before i need to do something, eg create a new table or whatever?|||well theres zero rows at the moment because im in the middle of building the application and setting up the database. But i am expecting approx 1000 new rows a day - not sure what limits are advisable as to how many rows before i need to do something, eg create a new table or whatever?You won't get close to the sort of volume where you need to start getting canny at 1000 inserts a day.
Agreed with Rudy - just put the column in your table and then select MAX() of that column to get the last update. Index the column. I think I would index ASC but there you go. Triger to update it.
BTW - one table - is it normalised?|||ok - sounds good to me - can i just write this out how i see it working from what you've said above:
1) Index time/date column Ascending
2) When getting the last update, use something similiar to:
SELECT MAX(timeDateStampColumn) as "LastEntry"
FROM table;
BTW - one table - is it normalised?
My application is based around sms text messages, each new row in my table is basically a new text message, there are only a few columns (id, message, mobile number, dateTimeStamp) so no need to normalise. I'm at the designing stages of the project so will certainly be more tables added later on.|||My application is based around sms text messages, each new row in my table is basically a new text message, there are only a few columns (id, message, mobile number, dateTimeStamp) so no need to normalise. That table structure sounds fine although I would disagree and say that the very early stages are absolutely the right time to normalise. I would also strongly recommend you design your complete database before worrying about requirements like "how do I find the time of the last SMS?".|||point taken ;)
thanks everyone for your excellent help (as usual) :D
Friday, March 9, 2012
Opinions Please
Hi, I have probably exhusted the topic of shapes etc... but I am still having a hard time determining the best solution for my problem:
I have several products, each with several specific properties:
Double Tee
-------------
Width | Height |Flange | Leg | Count
Column
--------
Width | Height
Round Column
------
Radius
Now originally I wanted to create a scalable table structure, so with the help of several people on this site (and SQL Team) I have developed the following :
tbShape
------
ShapeID | Shape | XSectionFormula
--------------
1 | Rect | Length X Width
tbShapeAttributes
------------
fkShapeID | AttributeID | Attribute
------------
1 | 1 | Length
1 | 2 | Width
tbProduct
------------
ProductID | fkShapeID | Product
------------
1 | 1 | Column
tbProductAttributeValues
--------------
fkProductID | fkAttributeID | Value
--------------
1 | 1 | 10
1 | 1 | 10
[/code]
From the above table structure I was able to select a product
and by obtaining the formula from the tbShape table, using a
cursor, replacing the Attribute names in the formula with the
attribute values from the tbProductAttributeValues table, using
dynamic SQL, I am able to determine the cross section of any
selected product.
The Problem now is, what if I need to apply different functions to
the data for any given product. This proves to be very difficult because
the attributes for the product are not necessarily consistent.
For Example, lets say the above was a slab 10 feet by 1 foot giving a cross section of 10 square feet. Because it is simple to get the cross sectional area, I can easily figure out the cubic feet of concrete used by multiplying the cross section by a length. But lets say the user want to get the cost / square foot? How is the application sure what attribute is the width of the product?
I guess what I am getting at is why the structure below is not any better then the one above?
tbTemplateCategories
------------
CategoryID | Category
tbTemplates
------------
TemplateID | fkCategoryID | Template |
-------------
tbDoubleTeeTemplates
-------------
fkTemplateID | Width | Height | Flange | Avg. Leg Width | Leg Count
tbWallTemplates
-------------
fkTemplateID | Width | Height
Now there would be a 1 - 1 relationship between the tbTemplates and tbDoubleTeeTemplates ON TemplateID - fkTemplateID. To add a new product, simple add the category, the new table, and then alter the Stored Procs which would use if() if else() statements based on the category to go to the appropriate template table.
Also, now I can write any customized functions for any product without the worry of user mispelling an attribute between the formula and attributes, etc...
Any opinions, thoughts on this would be appreciated!
Mike BAfter a little research, I found that this is refered to as sub-typing. This seems to be a very logical approach to the scenerio I have outlined. Even for shapes, this should be the way to go rather then trying to create a shapes - shape properties 1:M relationship. It seems to be more sound, manageable, and mantainable. So are those three attributes worth the tradeoff of flexibility? I am not convinced the flexibility is even lost seeing how easy it is to add a category then a sub_entity table for the attributes?
Mike B|||Mike-
We had a similar situation in our project and I tokk the exact same approach as yours. I have a tblProduct, tblAttribute, tblProductAttribute, tblProductAttributeValue. The challange was when the UI team asked me to return a product and all the attribute values in the same row. (The attributename should be the column name !!). The only way we could do it was through dynamic SQL. There was a lot of looping that goes on in the SP. I am not terribly pleased with this solution. While it gave us the flexibility of adding new products without changing the schema, there is a lot of performance hit we need to take that comes with it.
That's just me.
- cbarus|||Originally posted by sbaru
Mike-
We had a similar situation in our project and I tokk the exact same approach as yours. I have a tblProduct, tblAttribute, tblProductAttribute, tblProductAttributeValue. The challange was when the UI team asked me to return a product and all the attribute values in the same row. (The attributename should be the column name !!). The only way we could do it was through dynamic SQL. There was a lot of looping that goes on in the SP. I am not terribly pleased with this solution. While it gave us the flexibility of adding new products without changing the schema, there is a lot of performance hit we need to take that comes with it.
That's just me.
- cbarus
That is what I am afraid of. I am wondering if the flexibility is worth it if we were to only add one product / ohhh, who knows. I have been with this company for 10 years and I have never seen a new product.
Mike B|||Originally posted by sbaru
Mike-
We had a similar situation in our project and I tokk the exact same approach as yours. I have a tblProduct, tblAttribute, tblProductAttribute, tblProductAttributeValue. The challange was when the UI team asked me to return a product and all the attribute values in the same row. (The attributename should be the column name !!). The only way we could do it was through dynamic SQL. There was a lot of looping that goes on in the SP. I am not terribly pleased with this solution. While it gave us the flexibility of adding new products without changing the schema, there is a lot of performance hit we need to take that comes with it.
That's just me.
- cbarus
Are there any calucluations with the the attributes of your products? How are these handled?
Mike B
I have several products, each with several specific properties:
Double Tee
-------------
Width | Height |Flange | Leg | Count
Column
--------
Width | Height
Round Column
------
Radius
Now originally I wanted to create a scalable table structure, so with the help of several people on this site (and SQL Team) I have developed the following :
tbShape
------
ShapeID | Shape | XSectionFormula
--------------
1 | Rect | Length X Width
tbShapeAttributes
------------
fkShapeID | AttributeID | Attribute
------------
1 | 1 | Length
1 | 2 | Width
tbProduct
------------
ProductID | fkShapeID | Product
------------
1 | 1 | Column
tbProductAttributeValues
--------------
fkProductID | fkAttributeID | Value
--------------
1 | 1 | 10
1 | 1 | 10
[/code]
From the above table structure I was able to select a product
and by obtaining the formula from the tbShape table, using a
cursor, replacing the Attribute names in the formula with the
attribute values from the tbProductAttributeValues table, using
dynamic SQL, I am able to determine the cross section of any
selected product.
The Problem now is, what if I need to apply different functions to
the data for any given product. This proves to be very difficult because
the attributes for the product are not necessarily consistent.
For Example, lets say the above was a slab 10 feet by 1 foot giving a cross section of 10 square feet. Because it is simple to get the cross sectional area, I can easily figure out the cubic feet of concrete used by multiplying the cross section by a length. But lets say the user want to get the cost / square foot? How is the application sure what attribute is the width of the product?
I guess what I am getting at is why the structure below is not any better then the one above?
tbTemplateCategories
------------
CategoryID | Category
tbTemplates
------------
TemplateID | fkCategoryID | Template |
-------------
tbDoubleTeeTemplates
-------------
fkTemplateID | Width | Height | Flange | Avg. Leg Width | Leg Count
tbWallTemplates
-------------
fkTemplateID | Width | Height
Now there would be a 1 - 1 relationship between the tbTemplates and tbDoubleTeeTemplates ON TemplateID - fkTemplateID. To add a new product, simple add the category, the new table, and then alter the Stored Procs which would use if() if else() statements based on the category to go to the appropriate template table.
Also, now I can write any customized functions for any product without the worry of user mispelling an attribute between the formula and attributes, etc...
Any opinions, thoughts on this would be appreciated!
Mike BAfter a little research, I found that this is refered to as sub-typing. This seems to be a very logical approach to the scenerio I have outlined. Even for shapes, this should be the way to go rather then trying to create a shapes - shape properties 1:M relationship. It seems to be more sound, manageable, and mantainable. So are those three attributes worth the tradeoff of flexibility? I am not convinced the flexibility is even lost seeing how easy it is to add a category then a sub_entity table for the attributes?
Mike B|||Mike-
We had a similar situation in our project and I tokk the exact same approach as yours. I have a tblProduct, tblAttribute, tblProductAttribute, tblProductAttributeValue. The challange was when the UI team asked me to return a product and all the attribute values in the same row. (The attributename should be the column name !!). The only way we could do it was through dynamic SQL. There was a lot of looping that goes on in the SP. I am not terribly pleased with this solution. While it gave us the flexibility of adding new products without changing the schema, there is a lot of performance hit we need to take that comes with it.
That's just me.
- cbarus|||Originally posted by sbaru
Mike-
We had a similar situation in our project and I tokk the exact same approach as yours. I have a tblProduct, tblAttribute, tblProductAttribute, tblProductAttributeValue. The challange was when the UI team asked me to return a product and all the attribute values in the same row. (The attributename should be the column name !!). The only way we could do it was through dynamic SQL. There was a lot of looping that goes on in the SP. I am not terribly pleased with this solution. While it gave us the flexibility of adding new products without changing the schema, there is a lot of performance hit we need to take that comes with it.
That's just me.
- cbarus
That is what I am afraid of. I am wondering if the flexibility is worth it if we were to only add one product / ohhh, who knows. I have been with this company for 10 years and I have never seen a new product.
Mike B|||Originally posted by sbaru
Mike-
We had a similar situation in our project and I tokk the exact same approach as yours. I have a tblProduct, tblAttribute, tblProductAttribute, tblProductAttributeValue. The challange was when the UI team asked me to return a product and all the attribute values in the same row. (The attributename should be the column name !!). The only way we could do it was through dynamic SQL. There was a lot of looping that goes on in the SP. I am not terribly pleased with this solution. While it gave us the flexibility of adding new products without changing the schema, there is a lot of performance hit we need to take that comes with it.
That's just me.
- cbarus
Are there any calucluations with the the attributes of your products? How are these handled?
Mike B
Opinions on Organizing - Solutions and Projects
How are you organizing your solutions and projects? Are you creating a new
solution for every 'group' of reports? I am starting to wonder if I am
getting too granular and will have problems later on locating the reports.
All my reports have the same shared data source.
Right now, I am creating solutions for general headings - for example:
Productivity - obviously has all the productivity reports
Grants - I had seperated these out by individual grants (each grant has it's
own solution and own projects). Would it be easier to manage if all the
grants were in the same solution with different projects?We are organising our reports by having multiple projects under the one
solution.|||I'm developing custom reports for various customers, based on OLAP cubes and
SQL Server 2000 databases. These usually look similar, but are not
identical. This means I can't have the same data source for all reports.
I usually have one solution for each customer. Then I usually add different
projects under each solution, depending on the complexity of the order. I
usually start out with 2 projects: Dev and Final. Dev contains all reports
with static queries. Final contains the reports with dynamic queries based
on parameters. If the customer also orders some of our standard reports,
they go in 2 new projects, Standard Dev and Standard Final, in the same
solution.
Kaisa M. Lindahl Lervik
"WonderingFool" <WonderingFool@.discussions.microsoft.com> wrote in message
news:3E9D563B-78C7-431F-BAC5-47A971C6FFE1@.microsoft.com...
> How are you organizing your solutions and projects? Are you creating a
> new
> solution for every 'group' of reports? I am starting to wonder if I am
> getting too granular and will have problems later on locating the reports.
> All my reports have the same shared data source.
> Right now, I am creating solutions for general headings - for example:
> Productivity - obviously has all the productivity reports
> Grants - I had seperated these out by individual grants (each grant has
> it's
> own solution and own projects). Would it be easier to manage if all the
> grants were in the same solution with different projects?|||I use solution with multiple projects in Visual Studio 2005. Also I organize
projects using folders, so I am very flexible in the number levels of
projects categories.
solution for every 'group' of reports? I am starting to wonder if I am
getting too granular and will have problems later on locating the reports.
All my reports have the same shared data source.
Right now, I am creating solutions for general headings - for example:
Productivity - obviously has all the productivity reports
Grants - I had seperated these out by individual grants (each grant has it's
own solution and own projects). Would it be easier to manage if all the
grants were in the same solution with different projects?We are organising our reports by having multiple projects under the one
solution.|||I'm developing custom reports for various customers, based on OLAP cubes and
SQL Server 2000 databases. These usually look similar, but are not
identical. This means I can't have the same data source for all reports.
I usually have one solution for each customer. Then I usually add different
projects under each solution, depending on the complexity of the order. I
usually start out with 2 projects: Dev and Final. Dev contains all reports
with static queries. Final contains the reports with dynamic queries based
on parameters. If the customer also orders some of our standard reports,
they go in 2 new projects, Standard Dev and Standard Final, in the same
solution.
Kaisa M. Lindahl Lervik
"WonderingFool" <WonderingFool@.discussions.microsoft.com> wrote in message
news:3E9D563B-78C7-431F-BAC5-47A971C6FFE1@.microsoft.com...
> How are you organizing your solutions and projects? Are you creating a
> new
> solution for every 'group' of reports? I am starting to wonder if I am
> getting too granular and will have problems later on locating the reports.
> All my reports have the same shared data source.
> Right now, I am creating solutions for general headings - for example:
> Productivity - obviously has all the productivity reports
> Grants - I had seperated these out by individual grants (each grant has
> it's
> own solution and own projects). Would it be easier to manage if all the
> grants were in the same solution with different projects?|||I use solution with multiple projects in Visual Studio 2005. Also I organize
projects using folders, so I am very flexible in the number levels of
projects categories.
Saturday, February 25, 2012
Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF&quo
Hi,
I am facing a problem on a server which has raid 5 solution (3 disks), the raid controller went down 2 of the disks were off in the Bios.
We added the 3 disks to a different server identical in brand and architecture, the raid controller was able to reconfigure the virtual drive H:.
All files were there, we installed sql server 2005 on the new server, but when we tried to attach the database we got the error below:
TITLE: Microsoft SQL Server Management Studio
Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00000000af0000 in file 'C:\Data\mylog_log.LDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Operating system error 38(Reached the end of the file.) on file "C:\Data\mylog_log.LDF" during ReadFileHdr.
Could not open new database 'mydb'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 823)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476
BUTTONS:
OK
I tried the following steps but it always failed:
- create a new db with the same name of the lost db;
- put the db in emergency mode;
- stop sql service and replace the mdf file;
- start sql service;
- Run Dbcc checkdb('mydb')
we got the error below:
Msg 945, Level 14, State 2, Line 1
Database 'ism0506' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Any HELP please ?
Thanks,
Tarek Ghazali
Sql Server MVP
Well, the obvious question is where is your most recent backup of the database?
This situation doesn't look good. Losing 2/3 of a RAID 5 set means that you have lost a significant amount of data. Just because the file metada is there doesn't mean that the contents are there or intact. At a minimum, your log file is corrupt.
You can try CREATE DATABASE FOR ATTACH_REBUILD_LOG and see if you can get it going that way.
You WILL lose data, and most probably have inconsistencies due not being able to read the log file to do recovery.
Seriously restore from backup is your best option at this point.
|||Hi,
The issue is that the client did not backup since 3 weeks (his mistake) and the only solution is to restore from the corrupted files that they have. I tried your solution but it did not work.
This is the error that i got when i ran (CREATE DATABASE FOR ATTACH_REBUILD_LOG ):
The log cannot be rebuilt because the database was not cleanly shut down.
Thanks for your reply,
Tarek Ghazali
SQL Server MVP
|||Try this undocumented stuff provided by Kevin [MS].==========
1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran
4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:
DBCC rebuild_log('<db_name>','<log_filename>')
where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).
6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:
sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go
7. Turn off the updates to system tables by using:
sp_configure 'allow updates', 0
reconfigure with override
Go
============|||
Satya - none of that works in SQL Server 2005 (e.g. I removed the DBCC REBUILD_LOG command).
Tarek - you should call Product Support to help with recovering this.
Thanks
Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF
Hi,
I am facing a problem on a server which has raid 5 solution (3 disks), the raid controller went down 2 of the disks were off in the Bios.
We added the 3 disks to a different server identical in brand and architecture, the raid controller was able to reconfigure the virtual drive H:.
All files were there, we installed sql server 2005 on the new server, but when we tried to attach the database we got the error below:
TITLE: Microsoft SQL Server Management Studio
Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00000000af0000 in file 'C:\Data\mylog_log.LDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Operating system error 38(Reached the end of the file.) on file "C:\Data\mylog_log.LDF" during ReadFileHdr.
Could not open new database 'mydb'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 823)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476
BUTTONS:
OK
I tried the following steps but it always failed:
- create a new db with the same name of the lost db;
- put the db in emergency mode;
- stop sql service and replace the mdf file;
- start sql service;
- Run Dbcc checkdb('mydb')
we got the error below:
Msg 945, Level 14, State 2, Line 1
Database 'ism0506' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Any HELP please ?
Thanks,
Tarek Ghazali
Sql Server MVP
Well, the obvious question is where is your most recent backup of the database?
This situation doesn't look good. Losing 2/3 of a RAID 5 set means that you have lost a significant amount of data. Just because the file metada is there doesn't mean that the contents are there or intact. At a minimum, your log file is corrupt.
You can try CREATE DATABASE FOR ATTACH_REBUILD_LOG and see if you can get it going that way.
You WILL lose data, and most probably have inconsistencies due not being able to read the log file to do recovery.
Seriously restore from backup is your best option at this point.
|||Hi,
The issue is that the client did not backup since 3 weeks (his mistake) and the only solution is to restore from the corrupted files that they have. I tried your solution but it did not work.
This is the error that i got when i ran (CREATE DATABASE FOR ATTACH_REBUILD_LOG ):
The log cannot be rebuilt because the database was not cleanly shut down.
Thanks for your reply,
Tarek Ghazali
SQL Server MVP
|||Try this undocumented stuff provided by Kevin [MS].==========
1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran
4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:
DBCC rebuild_log('<db_name>','<log_filename>')
where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).
6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:
sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go
7. Turn off the updates to system tables by using:
sp_configure 'allow updates', 0
reconfigure with override
Go
============|||
Satya - none of that works in SQL Server 2005 (e.g. I removed the DBCC REBUILD_LOG command).
Tarek - you should call Product Support to help with recovering this.
Thanks
Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF
Hi,
I am facing a problem on a server which has raid 5 solution (3 disks), the raid controller went down 2 of the disks were off in the Bios.
We added the 3 disks to a different server identical in brand and architecture, the raid controller was able to reconfigure the virtual drive H:.
All files were there, we installed sql server 2005 on the new server, but when we tried to attach the database we got the error below:
TITLE: Microsoft SQL Server Management Studio
Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00000000af0000 in file 'C:\Data\mylog_log.LDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Operating system error 38(Reached the end of the file.) on file "C:\Data\mylog_log.LDF" during ReadFileHdr.
Could not open new database 'mydb'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 823)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476
BUTTONS:
OK
I tried the following steps but it always failed:
- create a new db with the same name of the lost db;
- put the db in emergency mode;
- stop sql service and replace the mdf file;
- start sql service;
- Run Dbcc checkdb('mydb')
we got the error below:
Msg 945, Level 14, State 2, Line 1
Database 'ism0506' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Any HELP please ?
Thanks,
Tarek Ghazali
Sql Server MVP
Well, the obvious question is where is your most recent backup of the database?
This situation doesn't look good. Losing 2/3 of a RAID 5 set means that you have lost a significant amount of data. Just because the file metada is there doesn't mean that the contents are there or intact. At a minimum, your log file is corrupt.
You can try CREATE DATABASE FOR ATTACH_REBUILD_LOG and see if you can get it going that way.
You WILL lose data, and most probably have inconsistencies due not being able to read the log file to do recovery.
Seriously restore from backup is your best option at this point.
|||Hi,
The issue is that the client did not backup since 3 weeks (his mistake) and the only solution is to restore from the corrupted files that they have. I tried your solution but it did not work.
This is the error that i got when i ran (CREATE DATABASE FOR ATTACH_REBUILD_LOG ):
The log cannot be rebuilt because the database was not cleanly shut down.
Thanks for your reply,
Tarek Ghazali
SQL Server MVP
|||Try this undocumented stuff provided by Kevin [MS].==========
1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran
4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:
DBCC rebuild_log('<db_name>','<log_filename>')
where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).
6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:
sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go
7. Turn off the updates to system tables by using:
sp_configure 'allow updates', 0
reconfigure with override
Go
============|||
Satya - none of that works in SQL Server 2005 (e.g. I removed the DBCC REBUILD_LOG command).
Tarek - you should call Product Support to help with recovering this.
Thanks
Subscribe to:
Posts (Atom)