4 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

I recently posted these questions in order to try and improve the performance for my queries.

DBA Q&A 1DBA Q&A 1

DBA Q&A 2DBA Q&A 2

One of the suggestions was that I should use a DATE() column rather than columns such as yr, yrmth(e.g. 201502), mth, day (which was how the data was given to me). Given the built in functions. However when I changed over to this, it slowed down a couple of my queries to the point where they aren't suitable for using on a web page. Following this, I decided to change back to the original way of using a year column, and a yearmonth column (theyre the granularities the page looks at), and my queries run in less than a few seconds.

I often see posts which 'condemn' this method of searching a table, however I can execute queries in a tenth of the time the 'less recommended way'.

If it is bad practice, why is it?

Is it a case of, if it works better that way, then use it? Or should I really not be seeing a 10x difference in query duration between the two?

Edit: My use case is write once, read lots, and the date values in rows will never change.

I recently posted these questions in order to try and improve the performance for my queries.

DBA Q&A 1

DBA Q&A 2

One of the suggestions was that I should use a DATE() column rather than columns such as yr, yrmth(e.g. 201502), mth, day (which was how the data was given to me). Given the built in functions. However when I changed over to this, it slowed down a couple of my queries to the point where they aren't suitable for using on a web page. Following this, I decided to change back to the original way of using a year column, and a yearmonth column (theyre the granularities the page looks at), and my queries run in less than a few seconds.

I often see posts which 'condemn' this method of searching a table, however I can execute queries in a tenth of the time the 'less recommended way'.

If it is bad practice, why is it?

Is it a case of, if it works better that way, then use it? Or should I really not be seeing a 10x difference in query duration between the two?

Edit: My use case is write once, read lots, and the date values in rows will never change.

I recently posted these questions in order to try and improve the performance for my queries.

DBA Q&A 1

DBA Q&A 2

One of the suggestions was that I should use a DATE() column rather than columns such as yr, yrmth(e.g. 201502), mth, day (which was how the data was given to me). Given the built in functions. However when I changed over to this, it slowed down a couple of my queries to the point where they aren't suitable for using on a web page. Following this, I decided to change back to the original way of using a year column, and a yearmonth column (theyre the granularities the page looks at), and my queries run in less than a few seconds.

I often see posts which 'condemn' this method of searching a table, however I can execute queries in a tenth of the time the 'less recommended way'.

If it is bad practice, why is it?

Is it a case of, if it works better that way, then use it? Or should I really not be seeing a 10x difference in query duration between the two?

Edit: My use case is write once, read lots, and the date values in rows will never change.

3 edited tags
source | link

I recently posted these questions in order to try and improve the performance for my queries.

DBA Q&A 1

DBA Q&A 2

One of the suggestions was that I should use a DATE() column rather than columns such as yr, yrmth(e.g. 201502), mth, day (which was how the data was given to me). Given the built in functions. However when I changed over to this, it slowed down a couple of my queries to the point where they aren't suitable for using on a web page. Following this, I decided to change back to the original way of using a year column, and a yearmonth column (theyre the granularities the page looks at), and my queries run in less than a few seconds.

I often see posts which 'condemn' this method of searching a table, however I can execute queries in a tenth of the time the 'less recommended way'.

If it is bad practice, why is it?

Is it a case of, if it works better that way, then use it? Or should I really not be seeing a 10x difference in query duration between the two?

Edit: My use case is write once, read lots, and the date values in rows will never change.

I recently posted these questions in order to try and improve the performance for my queries.

DBA Q&A 1

DBA Q&A 2

One of the suggestions was that I should use a DATE() column rather than columns such as yr, yrmth(e.g. 201502), mth, day (which was how the data was given to me). Given the built in functions. However when I changed over to this, it slowed down a couple of my queries to the point where they aren't suitable for using on a web page. Following this, I decided to change back to the original way of using a year column, and a yearmonth column (theyre the granularities the page looks at), and my queries run in less than a few seconds.

I often see posts which 'condemn' this method of searching a table, however I can execute queries in a tenth of the time the 'less recommended way'.

If it is bad practice, why is it?

Is it a case of, if it works better that way, then use it? Or should I really not be seeing a 10x difference in query duration between the two?

I recently posted these questions in order to try and improve the performance for my queries.

DBA Q&A 1

DBA Q&A 2

One of the suggestions was that I should use a DATE() column rather than columns such as yr, yrmth(e.g. 201502), mth, day (which was how the data was given to me). Given the built in functions. However when I changed over to this, it slowed down a couple of my queries to the point where they aren't suitable for using on a web page. Following this, I decided to change back to the original way of using a year column, and a yearmonth column (theyre the granularities the page looks at), and my queries run in less than a few seconds.

I often see posts which 'condemn' this method of searching a table, however I can execute queries in a tenth of the time the 'less recommended way'.

If it is bad practice, why is it?

Is it a case of, if it works better that way, then use it? Or should I really not be seeing a 10x difference in query duration between the two?

Edit: My use case is write once, read lots, and the date values in rows will never change.

2 edited tags
| link
1
source | link