June 20, 2011 at 3:51 pm
In building a data mart for use by the business, we've run into an issue with SQL's BIGINT type and Access. Yes, it's a well known fact that Access can't handle bigint. Everything says to cast to char or varchar.
Well, what are your options when you can't cast?
The reason I can't cast is because our business has something of an ad-hoc report they run twice a month. It contains roughly 25,000 values that must be searched for in the table. Well, since the business uses Access, we provided views to them with the primary key of this table converted to a char(10). The problem is, as you've probably guessed, is that searching on the char column in the view is completely nasty and results in a completely unacceptable query time (+10 minutes).
Here's a summary:
Table A:
primary_key bigint
column1 varchar(50)
View A:
Select
convert(char(10), primary_key),
column1
from Table A
Query
select primary_key, column1 from view A
where primary key in (25,000 values)
Special notes:
1) The query response is dreadful no matter if we set the where clause values to bigints or char strings
2) We cannot move to using ints as the PK.
So, what would be a suggestion for this conundrum? No amount of indexes on the SQL side will repair the response time.
June 20, 2011 at 7:03 pm
Can you embed the query that selects the 25,000 values into SQL Server as a view? (I presume it isn't really an In() statement with 25000 values - that should exceed the maximum length of a SQL string in Access.) If the 25,000 values can be done as a SQL Server table, then you could use a join on it, and that would let you link to the view in Access with just the returned records. You would need of course to CAST() the BigInt into a 10 character text field, but as long as you aren't trying to search or sort on it, things might work reasonably well. The other option that occurs to me is to use a pass-through query, but I'm not convinced that would help your situation.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply