Don't have SQL Server, so I can't speak directly about it, but.....
Have you considered placing the entire query into a separate sheet
within your workbook and then using VLookup, SumIF, a UDF, or whatever
function you need to use to get data from it? You can accomplish that
by Date--Import External Data-->New Database Query. Then, through
code, if needed, you could have the workbook automatically refresh the
query each time the workbook is opened.
question wrote:
> Hi,
>
>
>
> I have an excel report which used to access an SQL Server 2000 Analysis
> Services database. We have created a custom addin for the excel sheet using
> VBscript. The excel sheet used a VBscript function for getting data. The
> excel sheet has so many group id's. These group id's are passed as parameters
> to the VBscript function so as to get the data. There are some 30000+ group
> id's like this. The function will be called for each group id. This was
> working fine with our 2000 database.
>
>
>
> Ex:-
>
>
>
> Group ID Formula
>
>
>
> 1 function(1)
>
> 2 function(2)
>
> 3 function(3)
>
> ........
>
>
>
>
>
> We migrated our database to SQL Server 2005. After this we are facing a
> huge performance issue. The report which used to run in 2 mins is taking
> 15-20 mins now. While migrating to SQL Server 2005, we have transferred the
> VBscript to VB.net code.
>
>
>
> The same report is giving a better performance with the Microsoft addin
> available with Excel 2007. But as the microsoft addin doesn't have certain
> functionalities which we need, we are not able to use that.
>
>
>
> Any ideas why this is happening? Any possible areas which I should look
> into?
>
>
>
> The microsoft addin uses bulk querying and caching which results in
> faster performance. Any idea how to implement this?
>
>
>
> Please reply at the earliest possible...
>
> Thanks in advance....
|
Similar Threads
