|
Ive never found a decent solution to this problem, and as its coming up again I thought i'd throw this out to the wider community.
Im dealing with a database (SQL2005) which is:
1) Quite Large and growing rapidly
2) High traffic 24/7
As is often the case, one of the things that is required is reporting on various aspects of the data. However, as we know any reads (especially for the purpose of reports) will adversly affect the performance of the live activity going on.
In the past I have solved this mainly 1 way, and that is to on a scheduled basis to take the delta changes in the tables im interested in and write that data to another source and only report against this "achived" data. The problem with this is that you dont get "live reports" you get reports on data older than x hours (where x is your scheduled interval).
I am in a situation where reporting on Live data is pretty essential, but keeping the database as performant as possible is just as essential. To me these 2 requirements seem at loggerheads with each other. I obviously cant report on the live tables, as the overall application would slow to a halt. I also cant archive data on a timely basis because then the reports are not live.
Any thoughts? Is there some new-fangled sql tool that solves this problem? Have you faced a similar problem and found a suitable solution? Is there a gap in my sql knowoledge on this subject?
|
|
|
|
|
there's no single tool to solve your problem that i'm aware of, but in large high traffic databases everything comes into play. Index's clustered and otherwise, foreign keys, extra joins or operations, having conditionals in the where instead of part of the join can affect performance of a query or SP, even the physical layout of the data on the hard drive(s) come into play. I recently had to insert 400 million records into a database that wasnt large enough and it took forever waiting for the database to insert, grow, insert more, grow...so recreated the DB(not usually an option) to the correct size greatly improved performance of the inserts and any query run against them since it was all contigious data on the disk. With large amounts of inserts and updates to a table its possible any index could be corrupted and the DB is trying to re organize it each time, you may also have the wrong kind of indexes or too many of them on a table. you could run your query in query analyzer or parts of your query and look at the execution plan or the estimated execution plan to see what it taking up your time and try to optimize that section. after a few weeks of trying and some changes we can now search out 1 record in that 400 million, and tell you various things about it (no its not just one big table with everything it joins to several other tables with 1M+ records in them using a 7 or 10 char string) in less than a second.
btw we're using sql server 2k. it worked almost as fast on our old 2 proc server..now we have dual quad cores. you may have to upgrade your hardware, it may not solve the problem but it may make life a little better. without knowing your database or the data you are trying to report on i doubt anyone would be able to help you more than general pointers, if you have specific questions about queries or how to optimize tables feel free to ask.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Thanks for your answer, however it did not help with my question. Ive worked with SQL Server enough years to understand how indexes work, and how to write performant queries. 
|
|
|
|
|
A common solution is to use replication to maintain a reporting version of the database. Obviously is not quite real time, but its usually near enough. It also allows for some reindexing/denormailisation to aid reporting performance.
Hope this is of some interest.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Bob, yes it very much is of some interest.
I know SQL Server very well indeed, that is except for replication which ive not honestly come into contact with. I had a feeling it was relevant to this situation so I started reading up last night. You have just confirmed that my hunch was at least along the right lines.
Thanks again.
|
|
|
|
|
Bob, any idea what a reasonably lag time is for the replicated system (presumably configurable but guidlines?)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Naturally, it depends on transaction volumes and how important it is, but normally for a reporting database I would expect between 1 and 5 minutes, as opposed to a hot standby where it should be as close as possible to real time.
This msdn article gives a good overview of the overheads etc.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
As Bob said replication could be one solution. Especially transactional replication in your case.
Another option could be log shipping. With log shipping you may have to redesign your backup strategy, but on the other hand you gain a standby database, which can be used for reporting.
The need to optimize rises from a bad design
|
|
|
|
|
guys, i want to make a program using vb6. This program can update sql server 2000 server database based on data taken from MS access 2000. The algorithm that i applied is first, the program read the data from Access database ,then, it is saved temporary. next it will open the connection to the sql server then, compare the data between sql server and Access. If the data is different, the data in sql server will be replaced with the data in Access. Fortunately, until now i am stuck in how i can connect this two database together and compare the value.
Does the algorithm that i applied contain some mistakes and is there any better suggest algorithm for this problem?
Could anyone help me to give some code in essential part of the program?
Please. I need your help.
|
|
|
|
|
Its one way of doing it, but why not have 2 connections open, 1 to Access and 1 to SQL Server and cut out the temporary save? All you need to do then is run a loop over the 2 recordset (ensure you order the results the same way) and soe the details for insert/updatw/delete. When both recordsets are ended apply the changes.
There are probably several other ways too.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi
I want run a query like this:
select * from sp_MyTable_Select
i want make a query from result of some stored procedures
like this:
Select * from sp_fKeys Where COLUMN_NAME='X'
i know it is wrong
help me to found how can i do this....
tnx
|
|
|
|
|
Few issues:
- you cannot select from stored procedure directly. Just execute it to get the results
- do not start the name in your own procedures with sp_. That's reserved for system procedures.
- if you want to find foreign keys using a select statement, query sys.foreign_key_columns
The need to optimize rises from a bad design
|
|
|
|
|
you can do this with table functions
CREATE FUNCTION MyFunction()
RETURNS @tbl TABLE(COLUMN_NAME VARCHAR(50))
AS
SELECT
'A'
INTO @tbl
FROM someTable
then
SELECT * FROM MyFunction() WHERE COLUMN_NAME='A'
|
|
|
|
|
I saw the INTO @tbl and thought you were auto creating the column, as in Select * into MyTable from YourTable
Every had the hairs on the back of your neck stand up - that was my first reaction  .
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I wanted to connect to a remote SQl 2005 database via VPN on Windows XP. After configuring VPN connection on my local area Network. I dont seem to login to the Sql server database.
I am running Service pack 2.Does it have any thing to do with Operating System?
Could you please advice?
Thank you
|
|
|
|
|
By default SQL Server does not permit remote connections. You have to enable TCP/IP on SQL Server.
If that is not the issue, then you might want to look at your firewall settings.
|
|
|
|
|
hello,
i am new to programming and i have gotten far in learning C# in the .Net environment. i am at the point where i want to create Databases with SQL and link them to my programs but i cannot seem to find any headway or pointers in how to do that.
can anyone help me on this issue. i am searching the forums for any clue on how to start using SQL but without luck.
any suggestion is welcome. thank you
|
|
|
|
|
Try searching Google for ADO.NET, that should give you loads of articles.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
download samples here about how to connect to a database 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
how do you extract a photo from database in a JPEG2000 format and binary version of the photo ,i have a table in my database that stored photos like this eg dbfec061-a467-4833-8db3-9badfe086e7a.jp2
thanks
regards paula
|
|
|
|
|
Basically you can do something like:
- you read the data in binary form
- store the data to a temp file
- open the temp file using relevant program
This article is a little bit old, but it should get you to start: How To Read and Write BLOB Data by Using ADO.NET with Visual C# .NET[^]
Hope it helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
download a sample code here, search for something like load/save picture in sql server. You can store in database picture or gifs as well, videos i dont know but i think it works as well. 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi,
I want to retrieve all the column names by a query of a table in SQL SERVER 2005.
For Example: I Have a table: Table1, It has 5 columns: Column1,Column2,Column3,.......
So, I want to write a query that will display all these column names.
Table1
--------
Column1
Column2
Column3
Column4
Column5
I write this query to do the same:
Select column_name, data_type from user_tab_columns where table_name = user_details
But it is not working. Is this a right query or some mistakes are there.
|
|
|
|
|
astrovirgin wrote: But it is not working. Is this a right query or some mistakes are there.
Well, if its not working....
Where to start?
The error message you get is pretty obvious
Invalid object name 'user_tab_columns'.
I'll give you 1 hint (and there are several ways of doing this), look at information_schema.columns
Bob
Ashfield Consultants Ltd
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
