SQL weirdness when aliasing columns
-
I have a method that takes a SQL query and a file name, then converts that query into an XML-based Excel file. Works great. The code gets the schema of the query, and uses the column names to generate the column headers in Excel. This, too, works great. The problem is aliasing the columns into the headers that I want. I am using this notation:
SELECT
"First Name" = view.MeaninglessColumnNameImposedBySystem1,
"Last Name" = view.MeaninglessColumnNameImposedBySystem2,
"Address 1" = view.MeaninglessColumnNameImposedBySystem3,
...
FROM LongViewName AS view
WHERE etc=@etcWhether I have one column or 50, the query takes several minutes to run; the more data, the longer it takes. If I were to use
SELECT *, with no aliasing, I can return the data in a few seconds, regardless of how many rows are returned. I have also triedview.MeaninglessColumnNameImposedBySystem1 AS [First Name], which does not speed up the results. Presumably, this is an optimization issue where*lets SQL make assumptions that it cannot when each column is aliased. Any suggestions on what I can do with the query to speed things up? -
I have a method that takes a SQL query and a file name, then converts that query into an XML-based Excel file. Works great. The code gets the schema of the query, and uses the column names to generate the column headers in Excel. This, too, works great. The problem is aliasing the columns into the headers that I want. I am using this notation:
SELECT
"First Name" = view.MeaninglessColumnNameImposedBySystem1,
"Last Name" = view.MeaninglessColumnNameImposedBySystem2,
"Address 1" = view.MeaninglessColumnNameImposedBySystem3,
...
FROM LongViewName AS view
WHERE etc=@etcWhether I have one column or 50, the query takes several minutes to run; the more data, the longer it takes. If I were to use
SELECT *, with no aliasing, I can return the data in a few seconds, regardless of how many rows are returned. I have also triedview.MeaninglessColumnNameImposedBySystem1 AS [First Name], which does not speed up the results. Presumably, this is an optimization issue where*lets SQL make assumptions that it cannot when each column is aliased. Any suggestions on what I can do with the query to speed things up?Hi, You can use Column Alias like as follows
SELECT V.MeaninglessColumnNameImposedBySystem1 [First Name],
V.MeaninglessColumnNameImposedBySystem2 [Last Name],
V.MeaninglessColumnNameImposedBySystem3 [Address 1],
...
FROM LongViewName AS V
WHERE etc=@etcfor best practice go for Column names without any space like FirstName, LastName, Address1 ... Regards GVPrabu
-
I have a method that takes a SQL query and a file name, then converts that query into an XML-based Excel file. Works great. The code gets the schema of the query, and uses the column names to generate the column headers in Excel. This, too, works great. The problem is aliasing the columns into the headers that I want. I am using this notation:
SELECT
"First Name" = view.MeaninglessColumnNameImposedBySystem1,
"Last Name" = view.MeaninglessColumnNameImposedBySystem2,
"Address 1" = view.MeaninglessColumnNameImposedBySystem3,
...
FROM LongViewName AS view
WHERE etc=@etcWhether I have one column or 50, the query takes several minutes to run; the more data, the longer it takes. If I were to use
SELECT *, with no aliasing, I can return the data in a few seconds, regardless of how many rows are returned. I have also triedview.MeaninglessColumnNameImposedBySystem1 AS [First Name], which does not speed up the results. Presumably, this is an optimization issue where*lets SQL make assumptions that it cannot when each column is aliased. Any suggestions on what I can do with the query to speed things up?If you are returning the data to c# and then converting the datatable to Excel you could always rename the table columns after the retrieval.
Never underestimate the power of human stupidity RAH
-
If you are returning the data to c# and then converting the datatable to Excel you could always rename the table columns after the retrieval.
Never underestimate the power of human stupidity RAH
I am trying to avoid that: the idea is to have one general method that will take any of nine queries to generate different files than to have nine specialist methods that each take only a single query.
-
I am trying to avoid that: the idea is to have one general method that will take any of nine queries to generate different files than to have nine specialist methods that each take only a single query.
Somewhere you are defining the column names (you want it done on the entry query definition) either in 1 or 9 query constructors, I am suggesting moving the naming to post query execution, sticking that code into 1 method is trivial. Still it is a kludge b/c the aliasing should work :-O
Never underestimate the power of human stupidity RAH
-
Somewhere you are defining the column names (you want it done on the entry query definition) either in 1 or 9 query constructors, I am suggesting moving the naming to post query execution, sticking that code into 1 method is trivial. Still it is a kludge b/c the aliasing should work :-O
Never underestimate the power of human stupidity RAH
Like I said, the aliasing is working, but it takes a very long time for the query to return. In one test case, a query with aliased columns took almost 6 minutes to return; the identical query with unaliased columns took less than a second. This kind of time difference is pretty consistent, regardless of how many columns are requested, and scales closely to the number of rows returned (the more rows, the longer the aliased query takes.) It is very frustrating.
-
I have a method that takes a SQL query and a file name, then converts that query into an XML-based Excel file. Works great. The code gets the schema of the query, and uses the column names to generate the column headers in Excel. This, too, works great. The problem is aliasing the columns into the headers that I want. I am using this notation:
SELECT
"First Name" = view.MeaninglessColumnNameImposedBySystem1,
"Last Name" = view.MeaninglessColumnNameImposedBySystem2,
"Address 1" = view.MeaninglessColumnNameImposedBySystem3,
...
FROM LongViewName AS view
WHERE etc=@etcWhether I have one column or 50, the query takes several minutes to run; the more data, the longer it takes. If I were to use
SELECT *, with no aliasing, I can return the data in a few seconds, regardless of how many rows are returned. I have also triedview.MeaninglessColumnNameImposedBySystem1 AS [First Name], which does not speed up the results. Presumably, this is an optimization issue where*lets SQL make assumptions that it cannot when each column is aliased. Any suggestions on what I can do with the query to speed things up?I traced the problem to a piece of code that uses
SqlDataAdapter.Fillto populate aDataSet, then took to the Internet. It turns out that there is some optimization that SQL does with queries, where it tries to import indexes from underlying tables. This works fine as long as the columns in the view match up with the columns from the tables. When you alias the query's columns, however, things can degenerate into a quagmire of competing indexes; the delay is caused by SQL trying to wait out a resolution. You can get rid of the delay by instructing SQL to abandon arithmetic operations that result in an overflow or underflow. Huh? :wtf: The solution was to prefix the query withSET ARITHABORT ON;which tells SQL to just fly over the swamp rather than try to navigate through it. After the query, tack on; SET ARITHABORT OFF;to reset the server variable, mainly because there is probably a reason why it set to OFF in the first place. -
I traced the problem to a piece of code that uses
SqlDataAdapter.Fillto populate aDataSet, then took to the Internet. It turns out that there is some optimization that SQL does with queries, where it tries to import indexes from underlying tables. This works fine as long as the columns in the view match up with the columns from the tables. When you alias the query's columns, however, things can degenerate into a quagmire of competing indexes; the delay is caused by SQL trying to wait out a resolution. You can get rid of the delay by instructing SQL to abandon arithmetic operations that result in an overflow or underflow. Huh? :wtf: The solution was to prefix the query withSET ARITHABORT ON;which tells SQL to just fly over the swamp rather than try to navigate through it. After the query, tack on; SET ARITHABORT OFF;to reset the server variable, mainly because there is probably a reason why it set to OFF in the first place.Good bit of research, thanks for posting the solution, that with parameter sniffing goes into my odd box of delays to hunt for!
Never underestimate the power of human stupidity RAH