View Complete Post
If I use Microsoft.Jet.OLEDB.4.0 to retreive for example named range or sheet from Excel and use it as data source for some server control on the ASP page, everything works fine. But when I try to get table from Excel like this: "select * from [Table1]", debugger complains that database engine could not find object Table1. So how should I reference Table1 in Excel file so that the database engine could find it?
I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted
to a @temp table variable.
Here are some steps I have already taken...
The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."
Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.
Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!
The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.
The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).
Does anyone have any suggestions (or hope) for me?