Slow Performance When Exporting To Excel
Feb 1, 2007
Hi all,
I am experiencing some performance issue when using the Reporting Services (SQL Server 2000 SP 1) I have created some .rdl reports files and have uploaded them using Report Manager. The reports when run on their own is running fine and is loading within an ecceptable time. However, the problem comes when I start to choose to Export it to Excel.
The exporting process will take an exceptional long time to prompt user to save the Excel file and once it finishes downloading, it takes yet another exceptional long time to open up the Excel file. Can anything be done to rectify this slow Excel rendering issue???
Another thing that I have realised is also this. If the amount of data that the report contains is not alot and the exported Excel file is around 2MB odd or less then that, it will not experience the slowness in the rendering process. However, if the data is alot and the Excel file exceeds 3MB, the entire performance slows down tremendously and sometimes even not responding when opening the Excel file. It is the normal behaviour??
Thanks ~ mic
View 1 Replies
ADVERTISEMENT
Feb 22, 2008
Has anyone else exported data to Access? If so, is it a slow process in SQL Server 2005?
I am exporting 3,000 records to an Access database using a view and it has taken 20 minutes. I am using the Export Data wizard. Any tips on how to speed it up would be appreciated.
View 7 Replies
View Related
Mar 24, 2007
Hi all...
I need urgent help, about someting:
i've developed and deploy an aspnet web site (data works with sqlserver), but after a few minutes working with some users, the permormance slows and stop the site.
please help me what should i do......
View 3 Replies
View Related
Mar 30, 2000
I need to transfer a database from one server to another, I'm using the DTS utility because the servers have different sort orders. our database size is about 5GB which include about 2500 tables. Using DTS is taking many hours to transfer all objects and data. is there a better/faster way to do this?
Any help would be appreciated, Thank you
View 3 Replies
View Related
Mar 31, 1999
Help! I am new to SQL (6.5) and am designing a small database on a small LAN.
Everything seems to work fine until more than one user gets in the database.
It was originally an Access database and we upsized some of the information to SQL.
I have not upsized all the tables yet, and am not sure if that is causing a problem.
Since we are new to SQL we are trying to make all the changes in Access then upsize
everything. If this is the problem though, I will not be hesitant in upsizing.
Any ideas?
View 1 Replies
View Related
Dec 27, 2001
Since sp2 a number of applications, specifically web ado access has slowed very badly and I seem to be seeing a high cpu usage too. Internal jobs such as backups and table defrags are not affected. I'm on a 4 way box with ample hardware .. stats indexes etc. are fine .. client access is just slow. I suspect mdac but I can't pin anything down and it's driving me crazy !!!!!
I've even built a new box with a fresh install but to no avail.
help ???
View 3 Replies
View Related
Aug 24, 2004
I'm running the following statements. They have been running for 48 hours and counting... In performance monitor, I can see the system is steadily 100% disc bound. Any idea how to get status or ETA on these? Any idea how I can speed up such actions? This seems unusually slow. There are no errors in the Error Log. Does other DBMS systems (such as Oracle) handle such scenarios more quickly or with better status information?
INSERT INTO Domains (Domain)
SELECT DISTINCT Domain FROM Stages WHERE Domain NOT IN (SELECT Domain FROM Domains)
UPDATE Stages SET DomainID = (SELECT Domains.[ID] FROM Domains WHERE Domains.Domain = Stages.Domain)
Stages is 173 million records
Domains is 2.4 million records
This is running on pretty decent hardware:
Windows 2000 SP5
SQL Server 2000 Enterprise Edition SP3a
Dual 3.06 GHz Xeons with HyperThreading enabled (4 virtual CPUs)
4GB RAM (OS 3GB switch is enabled to give SQL Server 3GB of RAM)
70GB SCSI boot drive
Data/Transaction Log is on a RAID 5, 30 spindle, fibrechannel SAN
View 14 Replies
View Related
Aug 30, 2006
Hi - I have MS SBA2007beta and Office Outlook 2007 BCM Beta. Both use SQL2005 and they are both as slow to point of being frustrating. Outlook2007 without BCM is fine and every other application runs fine if no SQL server apps are present.
I have this running on a 2.8ghz desktop with 1G memory and a Laptop of 1.8ghz and 1.5g memory. The laptop is far faster.
When SQL apps are running everything is slowed.
View 1 Replies
View Related
Jun 14, 2001
I have DTS package which exports the data from myTable to myXLSheet - works fine, except this appends data to the existing myXLSheet.
Is there any way of creating a new sheet or clearing out the existing one first???
Thanks
View 2 Replies
View Related
Apr 3, 2007
Hi,
Ive installed and imported a db into SQL2005 Express. I now need to export the db to an excel file but I cannot find the import/export feature. Ive tried navigating to the db within the SMSE inteface and right clicking but there is no options available to carry out the task.
Any ideas guys, stuck on this one
DW
View 3 Replies
View Related
Dec 5, 2007
Does any one know how to name the tabs in reporting services when you export the date to excel. example I want sheet1 to be Summary. Can this be done in rporting services?
View 2 Replies
View Related
Jul 20, 2005
HiI am using the DTS package with SQL SERVER 2000. I have a table, and in oneof the columns are links to websites. I am trying to export this to Excel2002 SP3a typical text string stored in the table is=HYPERLINK("www.asite.com","Click")I had hoped the field would be transferred exactly as is and so would appearas a hyper link in the Excel document it creates. Unfortunateld it seems toautomatically put a ' in front of it so if I click on the cell, in the barat the top I get'=HYPERLINK("www.asite.com","Click") - Notice the apostropheso the full text is displayed in the spreadsheet (rather than the wordClick)Does anyone know of a way to get rid of the ' thats being added onthanks in advanceAndy
View 2 Replies
View Related
Mar 7, 2008
Hi,
I have 5 subreports, where each is wrapped in its own rectangle, in one report. The report renders successfully. However, when I try to export the results to Excel, I receive the following error
An error occurred during local report processing.
An error occurred during rendering of the report.
An error occurred during rendering of the report.
Item has already been added. Ket in dictionary: '_41_201' Key being added: '_41_201'
Any ideas?
The reports defined in the subreports contain drill through links to each other. These reports were originally designed to be drill through reports. However, my user will like to export them into one workbook.
View 1 Replies
View Related
Jul 11, 2007
Hi All,
I am creating reports using SSRS 2005.
After deploying my reports to the reporting server i want my reports to be exported to excel.
But when i perform this the formatting of the report gets changed in excel.
The cell in excel gets merged.thus the structure gets distorted.
Does anyone have any idea in this regrard?
Why is it so and what is the solution for the same?
Thanks
Regards
Saurabh
View 14 Replies
View Related
Oct 23, 2007
Hi,
Sorry if this is a stupid question, RS isn't my area of expertise, a customer has asked if it was possible to export all the data in an RS report onto one Excel sheet.
She has created a report off an AS2005 cube, there are 71 pages, is there a way of increasing the number of rows per page or even putting them all on one page? Having some 30 sheets in a spreadsheet seems a little excess.
Thanks in advance
Matt
View 5 Replies
View Related
Jan 31, 2000
Hello,
I have the same database on two different servers. One for production and one for testing. A view that I use runs in less than 2 seconds on the test system, but takes almost 2 minutes on the production server.
What I have noticed is on the test server the view will use an index. The production server ends up scanning a whole table. All indices are the same on both machines for the tables involved and I have updated the statistics. I even went through the process of creating a new table with its indices for the table that is being scanned. Both machines have had service pack 1 installed on them.
Any ideas?
View 1 Replies
View Related
Jan 28, 2003
An Application Times out with tons of SQL Server Locks but there is no log of any errors in SQL Servers. I checked all the Indexes and I reindex it but still same issues. CPU is 100% full at that time and users unable to do anything. Any ideas on how to fix it
View 1 Replies
View Related
Jun 5, 2004
Hi
We are facing performance related problem using Sql server 2000.
We have one stand alone P4 Pc (128 ram) and around 30 users access the sql server through network.
We have written our aplication in VB 6 and backend as Sql Server 2000. We have used Stored Procedure where ever necessary. We have used cursor location as Server side.
When we start with 5 users it is not slow, when all the users say 30 comes in it is slow down.
Can some one help to find out what is the problem.
Thanks
View 1 Replies
View Related
Nov 29, 2005
I'm still new to SQL Server so some of my lingo/verbage may be incorrect, please bare with me.
The company I work for relies strictly on ASP and SQL Server for 85% of it's daily operations. We have some Access projects and some VB projects as well, but for the majority it's ASP and SQL Server.
Previously we had 2 T1 lines with something like 3MB a piece and a handfull of Dell Servers. Our main server is also a Dell running Windows Server 2003 and is hosted through a reputable company here in town. They have a host of fiber lines running all over so I know we're getting good throughput. We've actually just upgradded to a DS3 but we're still working out the kinks with that. Anyway, I just want to eliminate that up front - we have great connection speeds.
The problems lies, I believe in our database design. The company supposedly had a DBA come in and help setup the design some 3 or 4 years ago, however even with my limited knowledge I feel like something is just not working right.
Our main table is "Invoices" which is obviously all of our Invoices, ever. This table has an Identity field "JobID" which is also the Clustered Index. We have other Indexes as well, but it appears they're just scattered about. The table probably 30-40 fields per row and ONLY 740,000 rows. Tiny in comparison to what I'm told SQL Server can handle.
However, our performance is embarassing. We've just landed a new client who's going to be brining us big business and they're already complaining about the speed of their website. I am just trying to figure out ways to speed things up. SQL is on a dedicated machine I believe with dual Xeon processors and a couple gigs of ram. So that should be ok. THe invoices table I spoke of is constantly accessed by all kinds of operations as it's heart of what we do. We also have other tables such which are joined on this table to make up the reporting we do for clients.
So I guess my question is this. Should the Clustered Index be the identify field and is that causing us problems? We use this field alot for access a single Invoice at a time and from what I understand this makes it a good Clustered Index, because the index IS the jobID we're looking for. But when it comes time to do reporting for a client, we're not looking at this field. We just pull the records for that Clients Number. And we only have 1400 clients at this point. So if we were to make the "ClientID" field the Clustered Index, it would much faster to Zero in on the group of Invoices we wanted because the ClientID is ALWAYS included in our queries.
But because a "DBA" came in to design this setup, everyone is afraid to change it. I guess it's hard to explain without people sitting here going through the code and look at the structures of all our tables - but I guess what I need is like a guide of what to do to easily increase performance on SQL Server and the proper use of Clustered and Non-Clustered Indexs and how to mix and match those.
Sorry I wrote a book.
Ideas? This place has always helped me before, so thanks in advance!
View 14 Replies
View Related
Feb 11, 2007
Hi,
I'm executing a stored procedure in my local LAN which executes another one in a loop and I update a Table. The number of loops is about 6300.
This operation takes about 25 seconds in my local LAN.
Then I try to execute though in a VPN which has an upload speed of 256 kbps. I open query analyser connect to the remote server which is must faster than mine and I just write exec mystoredprocname in order to execute the procedure. The performance is very very slow.
In 7 minutes 180 loops are completed out or 6300.
I really cannot understand this. What is the reason of such slow perfomance?? My ADSL model displays no activity when the procedure is executed. I just use the PRINT method in MSSQL in order to display the progress of the operation. I tried to comment it out but with no difference.
I also use SET NOCOUNT ON in order not to display the update results.
Can someone explain me the cause for this? Are there some tricks in order to improve the performance when a slow connection is used like a ADSL with a static IP? It seems that something wrong is happening here.
Best Regards,
Manolis Perrakis
View 1 Replies
View Related
Jul 31, 2007
dear friends,
i have dropped many objects and recreated in a database.suddenly my database became very slow. so please any one of friends give solution.
View 9 Replies
View Related
Feb 7, 2008
I have a bunch of packages that take views and create tables from them. Some of the views are rather complex, but the packages themselves are very simple... drop and re-create a table using the data from a view on the same server. We create a new DB for each year, and this year we've upgraded to a new server with SQL 2005, so our DTS packages on the 2000 SQL server had to be recreated in SSIS on the new server. No problem, as I said the packages are really simple. But when I create the packages in SSIS they now take an extremely long time to execute, and I cannot figure out why.
For instance, one DTS package would take approximately 5 minutes to run when the view contained hundreds of thousands of rows and the underlying tables contained millions. But now, even with MUCH smaller tables (since it's the beginning of the year, new DB) the SSIS package I created on the new server takes over an hour, literally. The view that the SSIS package is using to create the table only takes about 15 seconds to execute in management studio (only about 16,000 rows). How can this possibly take so long??
the new server is virtually the same hardware-wise... 4 x 2400mhz, 4gb ram, win2k3 server
View 14 Replies
View Related
Jul 23, 2005
I have a stored procedure that has some problems with slow performance.The procedure has 2 parameters@firstnamevarchar(100)@lastnamevarchar(100)These parameters can have values like a%, that is wildcard searches.The strange thing is that if one of the parameters has the value %, andthe other one a%, the performance is very bad.If i subsistute the variables with exactly the same values hardcoded inthe where-clause, the query is very fast.If both variables has some characters prepending the percent sign, theperformance is better.SELECT distinct u.user_id, u.username, u.status_idFROM statusnames sn, statuses s, users u, users_persons up, persons p,users_roles urWHERE p.lastname like @lastnameAND p.firstname like @firstnameAND s.status_id = u.status_idAND sn.statusname_id = s.statusname_idAND u.user_id = up.user_idAND up.person_id = p.person_idWhat makes SQL server behave so differently with variables andhardcoded values?
View 6 Replies
View Related
Feb 19, 2008
Hi,We have shifted one of our data processing to a new instance of SQL2005. For this a new DB was created in SQL2005 and it is not anupgrade of SQL200 DB.The data processing application is a VB6 application that runs a batchprocess to insert about4.5 million records everyday in this SQL 2005 database. I am usingADO(ver 2.6) Connection object to execute T-SQL insert statements toinsert the 4.5 mill. records.This same application used to insert the data in the SQL 2000databaseand used to take about 2.5 hours to do so. After moving it to SQL2005 the performance has really detriorated and it now takes morethan6 hours.Is there any configuration change that I need to do in SQL 2005?Please help.Note: There is also an instance of SQL 2000 running in the sameserver.TIA,Tawfiq
View 3 Replies
View Related
Apr 12, 2007
I was transfering more that 100,000 records from flat file to sql table
It took about 1 hour.Is this the way it is?????i used oledb command.
As the data passes by i got to insert to several table.Like i insert some of incoming data to one table then get the key from that table and insert rest of the data with the key field from previous table to another table.
In this case i felt OLedb would be best as we can use query.
I cannot use oledb destination as it has only error output(to insert some of incoming data and i want to have a look up to get the key but oledb des has only error output)
i cannot use sql destination as the database is sql server 2000.It dosent let me.
How can i increase the performance????Please let me know
View 9 Replies
View Related
Dec 10, 2007
Hi.
I have successful upgraded from Windows 2000 / SQL Server 2000 to Windows 2003 / SQL Server 2005. Here is the process:
- Install Windows 2003 / SQL Server 2005 on new VM instance
- Detached SQL Server 2000 database -> Copy data to the new server and Attached to SQL Server 2005
- Run scripts to restore user login
- Change Compatibility level to: SQL Server 2005 (90)
Now, open 3rd party application and connect to the new database server (via IP address), the application was connecting ok and access data without no problem. However, database take more than 5 minute to connect/open instead of 30sec via the old the server?
Please adivse what could be the issue? Thanks!
View 7 Replies
View Related
Oct 5, 2001
I'm trying to export a table from SQL7 to Excel. Only thing is that I'd like to specify the Excel spreadsheet filename at run time. The name of the spreadsheet needs to contain the current date e.g. "table1_20011005.xls", "table1_20011006.xls". Is there any way I can do this ? I've looked at DTS but it seems you need to specify the spreadsheet file name and cannot alter it.
View 2 Replies
View Related
Jan 3, 2003
Hi,
I am having some problems exporting to Excel using SQL 7.
I have a DTS package which runs a query that returns one value and pumps the data to an Excel spreadsheet. Everything is working except the data is not populating the correct cell in the spreadsheet. How can I control which cell SQL pumps the data to?
Thanks.
View 3 Replies
View Related
Aug 14, 2014
The date in sql appears like this '07/25/2013 00:00:00' but when I export to excel the date shows like this '22-JUL-81 12.00.00.000000000 AM'. When I change format in excel nothing happens.
View 6 Replies
View Related
Feb 19, 2007
Kevin writes "Please forgive me if this is a stupid question. I am working in an environment where we are using SQL Server 2005 and Excel 2003. We have a custom .NET 2005 application which runs against the SQL Server. Users frequently create ad hoc queries in Management Studio to pluck out certain info. They would like to save the results down into an Excel spreadsheet for further analysis; However, we haven't come up with an elegant solution to save these results in Excel and keep the column headings and data types for each column. I have seen transact-sql code to do this in the past using the OPENROWSET command to write to an Excel file; However, I am not confident that this is the best way to skin this particular cat so I thought I would run it by you. So, my question is, What is the BEST way to save ad hoc query results from SQL Server to an Excel spreadsheet so that I preserve the column headings and data type of each column?"
View 2 Replies
View Related
Jul 15, 2007
Hi,
I am Sree Raj. New to SQL Server.
I am trying to export data to excel and it is giving an error.
Here is the query and error:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:SQL ServerEmp_Personal_Data.xls;', 'SELECT * FROM [Sheet1$]')
SELECT * FROM Emp_Info
error is:
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
Can anybody please help me what is this error about & how to fix this.
Thanks in advance
Sree Raj
View 1 Replies
View Related
Jul 23, 2005
Hi all,I've seen this noted in many posts, but nothing I've checked out givesme any clue on how to do this.Basically as my topic says, I have a DTS and I simply need to exportsome data from a table in MS SQL 2000 to an Excel spreadsheet. I alsoneed to automate this process so it can run nightly and each new day anew spreadsheet will be on a network share for us to pick-up.Can someone point me to the right direction? This needs to be donetotally through the DTS script, so no ImportExport wizard or anythingmanual.Thanks --Sam
View 1 Replies
View Related
