|
Dear gurus,
I am developing a charting application that should plot [double] values against [DateTime] values. In a single turn, there can be as much as 10 million data points.
What I do is to extract the data from SQL Server and use ChartDirector to plot it. For about 1 million data, extraction takes 25 seconds and plotting takes 20 seconds.
This is a lot of time to wait for user. Can you please suggest me any?
like sampling to reduce the data, or fast extraction, or fast plotting?
Thanking you!
|
|
|
|
|
The obvious one is to reduce the number of data points over the entire time frame IE get 1 data point per week/month. Allow the user to drill into a specific week/month at which time you may show all or data points. You can't actually see 10mio datapoints, the values would become irrelevant over such a large size.
BTW 45 sec to get 10mio rows of data seems eminently reasonable to me.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you, Mycroft Holmes.
But the problem is that I cannot afford to miss out the outliers among the data. If I pick out 1 data poing per month, there is high chance that any outlier may miss out.
Should taking the mean withing the time span solve the problem?
Thanking you.
|
|
|
|
|
mahabir wrote: If I pick out 1 data poing per month,
There is a trading graph the shows hi/low and volume for period per period (Day per year). That will solve your problem. There are at least 3 other methods of displaying the data without dumping our millions of rows!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
By using ADO to deal with excel, I can create the table in Excel successfully, but when I try to insert a new column, the SQL command failed.
ALTER TABLE tablename INSERT COLUMN col1 FLOAT
Why?
system
|
|
|
|
|
Question comes instantly to mind - WHY THE FREAKIN HEEL ARE YOU USING EXCEL AS YOUR DATABASE. Unless there is an overriding business reason to do this you are setting yourself up for a disaster. Please use SQL Express if possible, Access if you really must use office but Excel NNnnnnnooooooo!
Sorry that I cannot help you with this I am not even sure it is possible. Quite possibly I would not even if it is possible!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yes, the customer need a report in an Excel file.
system
|
|
|
|
|
followait wrote: customer need a report in an Excel file
Ok thats a different problem. Do not create a database for outputting to excel. If you do a search in the articles here you will find many examples for output to excel from both SQL and UI.
You have 2 choices, output a csv file and allow excel to do the conversion when it is opened. This is by far the simplest method and probably the widest used.
Alternatively you can manually create an excel sheet by manipulating the excel application object. This will give you formatting options but will take a lot more work and require the excel application to be installed on the client computer.
3rd option it to use a reporting engine to render an excel report. This give you the best format but will require you get a handle on a report engine.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The client I'm working for is using LINQ to SQL behind a BLL of sorts. So each CRUD operation gets its own context. Here's an example of the Save method:
<br />
public static Step Save(Step step)<br />
{<br />
using ( KRATDataContext context = new KRATDataContext( Helpers.EnvironmentState.CurrentConnectionString ) )<br />
{<br />
Step oldStep = step.StepID > 0 ? context.Steps.Single( p => p.StepID == step.StepID ) : null;<br />
<br />
if ( oldStep == null )<br />
{<br />
context.Steps.InsertOnSubmit( step );<br />
}<br />
else<br />
{<br />
oldStep.Description = step.Description;<br />
oldStep.PotentialHazard = step.PotentialHazard;<br />
oldStep.ActionsToReduceRisk = step.ActionsToReduceRisk;<br />
oldStep.InitialRiskCellID = step.InitialRiskCellID;<br />
oldStep.PostRiskCellID = step.PostRiskCellID;<br />
oldStep.DateCreated = step.DateCreated;<br />
oldStep.RiskAnalysisID = step.RiskAnalysisID;<br />
oldStep.DisplayIndex = step.DisplayIndex;<br />
}<br />
context.SubmitChanges();<br />
<br />
return step;<br />
}<br />
}<br />
<br />
I have implemented functionality which allows a user to change the display order of a "Step" record by clicking an up/down arrow which essentially swaps the value of DisplayIndex for two separate records. Like this:
<br />
protected void Swap(int stepId1, stepId2)<br />
{<br />
Step step1 = Step.GetOne(stepId1);<br />
Step step2 = Step.GetOne(stepId2);<br />
<br />
Swap(step1, step2);<br />
<br />
using( System.Transactions.TransactionScope txCtx = new System.Transactions.TransactionScope()){<br />
Step.Save(step1);<br />
Step.Save(step2);<br />
<br />
txCtx.Complete();<br />
}<br />
}<br />
The important fact being demonstrated here is that each save operation has its own DataContext. So when the 2nd call to Save() is made I get an exception because I have explicitly turned off DTC (ie. I don't allow remote DTC operations). But these two records are in the same table.
So here's what I'm looking for:
1) Can someone else confirm this for me, but duplicating the error I get.
2) Is there a way around this, other than using the same DataContext object?
3) I'll need to double check, but this doesn't happen to me when I use the EnterpriseLibrary connection objects with TransactionScope. So is this a bug? Or at least something that should be changed so it works the way EnterpriseLibrary does?
#2 is important because while fixing this issue for the specific case above (both objects are based on the same database table) I have other cases where I'll have to create specialized methods in my data access layer which allow me to use the same data context so I can wrap operations on different tables with the same DataContext.
As always, I appreciate the help.
[Edit]
Here's the exception detail:
System.Transactions.TransactionManagerCommunicationException was unhandled by user code
Message="Communication with the underlying transaction manager has failed."
Source="System.Transactions"
InnerException: System.Runtime.InteropServices.COMException
Message="Error HRESULT E_FAIL has been returned from a call to a COM component."
Source="System.Transactions"
[/Edit]
|
|
|
|
|
Ok, well I came up with a solution for this (for anyone interested).
The DataContext is not aware of any other contexts (through thread local storage) in any way. So I basically decided to use thread local storage to store my DataContext for the current thread (HttpRequest).
Here's how it works. The DataContext classes generated for a dbml file is a partial class, so I added a class file to the same project which contains a static factory method, GetDataContext(). GetDataContext checks HttpContext.Current.Items to see if a DataContext object already exists. If not, it creates and new one, stores it in HttpContext.Current.Items and the returns the new instance. If the object already exists, it just returns the existing object to the caller.
<br />
public static DataContext GetDataContext()<br />
{<br />
if(HttpContext.Current == null)<br />
return new DataContext(connectionString);<br />
<br />
if(HttpContext.Current.Items.Contains("DataContext"))<br />
{<br />
return (DataContext) HttpContext.Current.Items["DataContext"];<br />
}<br />
<br />
return new DataContext(connectionString);<br />
}<br />
Now, I am able to use TransactionScope to wrap my business logic without having to use DataContext directly. Plus there are other benefits, my Save method no longer requires the additional database call to retrieve the record and copy the changes before calling SubmitChanges(). So compared with my previous post, my Save method now looks like this:
<br />
public static Step Save(Step step)<br />
{<br />
KRATDataContext context = KRATDataContext.GetDataContext();<br />
<br />
if ( step.StepID <= 0 )<br />
{<br />
context.Steps.InsertOnSubmit( step );<br />
}<br />
<br />
context.SubmitChanges();<br />
<br />
return step;<br />
}<br />
Which requires one less database call, always a plus  . This all solves my Transaction problem because it uses the same connection object (because we're using the same DataContext which owns the Connection object) and so there is no longer any distributed transactions (DTC) required.
|
|
|
|
|
My database table has got 5 fields and through my C#.Net code I'm adding the 6th field (using dataset and .WriteXmlSchema() )...........but it works fine for the first time............aftr tht since the DataSet schema has already been updated with the 6th column, it throws an error "6th Field already exists in database table"............
so i just want to do something like this :
if (check whther Access Database Column is already present for the ABC Table)
{
do {nothing}
}
else
{
do {add the 6th column - for which i already have the code}
}
|
|
|
|
|
Post your real code to see where is problem.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hello Friends,
Please let me know if there's an easy to change the Report Title of Crystal Report at Runtime Coz I could see that once you create the Crystal Report there's no option to change the title else I have to start from the scratch creating a new Crystal Report and at that point of time adding the title what I want.
Please share if you know something about this.
Thanking you in anticipation.
|
|
|
|
|
yes you can change it...assuming you mean some text thats displayed as a title. there isnt much you can do with the version of CR(2008) at runtime, at least as far as dynamic charts go, but assuming you know what section your title is in, you can loop through that sections report objects, looking for text objects, i cant remmeber if there are title objects, and then you can change the text of the objects yo ufind...something like
int i = 0;
CrystalReport rpt = new CrystalReport(the class should be the name of your rpt file - the extension)
foreach (TextObject to in rpt.Section1.ReportObjects)
{
to.Text = i;
i++;
}
this way you can see what the index of the box you want changed is.. i recomend reading anything by Brian Bischof(sp?) or picking up one or more of his books if you'll have to deal with CR alot, even ones written in an older version helped me alot. i think i have Crystal Reporting for .NET.
I gave up on CR a while ago so there may be an easier way, this was just the first method that came to mind. hope it helps.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
If you don't need to do it at run time, then you can change the title of the report by right mouse clicking on the file name (myreport.rpt), go to properties, then the Summary tab and change the name of the report. I thought this was a bit strange, but it works for me. Crystal Reports 10.
|
|
|
|
|
I didn't meant to change the name of the report. I'm talking about the last section in the Crystal Report creation wizard which asks you to enter a title for the report and that title gets displayed every time you execute the report ( e.g Sales Summary Report - 2008 ). The top most title on the report page.
To be more clear, I'm talking about the : PAGE HEADER (SECTION 2) of the Crystal Report. And I don't see any option to change the title name.
Please comment.
|
|
|
|
|
Hi,
After the report is created by the wizard:
- Select from menu Report / Report parameters
- Add a string parameter
- Right click on the title and select Expression
- you can see the static text in the expression, replace it with the parameter
- just before launching the report, deliver the value for the parameter
Hope this helps,
Mika
|
|
|
|
|
I’m creating a SQL script for an installation package that I’m working on. I want to parameterize the database creation process. Wants to be able to set the database name in a variable, and have the SQL script use that variable to create the database and the necessary tables.
So far … this part works to create the database
USE [master]
DECLARE @DBName varchar(60)
DECLARE @DBDir varchar(100)
DECLARE @SQL varchar(MAX)
SET @DBName = N'Test'
SET @DBDir = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\'
SET @SQL = N'CREATE DATABASE ' + @DBName + ' ON PRIMARY
( NAME = "' + @DBName + '",
FILENAME = "'+@DBDir + @DBName + '.mdf" ,
SIZE = 4048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = "' + @DBName + '-01_log",
FILENAME = "'+@DBDir + @DBName + '_log.ldf" ,
SIZE = 4096KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'
EXEC (@SQL)
EXEC dbo.sp_dbcmptlevel @dbname=@DBName, @new_cmptlevel=90
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC (@DBName + '.[dbo].[sp_fulltext_database] @action = "enable"')
End
However, I can’t seem to switch to the database in script, and create the tables in it
I’ve tried -> EXEC ('USE ' + @DBName)
And also -> USE @DBName
Could you help me please ? Thank you for your time
Cecil L. Phillip
|
|
|
|
|
When you execute Exec('User' + @DBName), it is actually working. Since the exec syntax opens a new connection, your not retaining the new database in the connection your script in on. Your going to have to use 3 point qualifying names, or make a @Sql like:
Set @sql = 'Use ' + @DBName
+ ' --Do Something'
Then you'll probably run into errors about objects that must be ran first in the batch (stored procedures, for example), which "GO" will not fix because that is not a real sql syntax.
|
|
|
|
|
how would i use 3 point qualifying names and the @DBName with a create table statement or an insert ? Could you give an example ?
|
|
|
|
|
Set @Sql = 'Create Table ' + @DbName + '.dbo.TestTable '
+ '( col1 int )'
Exec (@sql)
"3 point qualifying names" is Database.Schema.Table.
|
|
|
|
|
gotcha ...
I already generated the scripts to create the tables, populate them, and create the other db objects and its a lil over 5000 lines of sql code.
I guess there is not way to maintain the current connection and change the current database huh ... hhmmm
Thank you tho.
|
|
|
|
|
|
Another point of view in creating an installation that might be useful for you.
If you create an initial database (in your own server) including all the necessary tables, views, stored procs etc. you can then:
- detach the database
- copy the data files to another location
- attach the database
After this you have a copy of the initial situation. What you can do with this is, that you can copy the files to another server (where you are installing your database) and again attach it in that sql server. This way you actually don't have to actually create any database or table creation script, simply *put* the database where you want it to be and attach it.
What you must do is to take care of:
- login creation
- user creation
- backup arrangement
- other feature used that are not stored in your database
For further info, take a look at sp_detach_db and CREATE DATABASE ... FOR ATTACH
Mika
|
|
|
|
|
What I find horrifying is that you allow the user to set the database name!
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
