|
Ask yourself these questions:
1 - Do you notice the difference between <1ms and 40ms?
2 - Do you do a lot of transferring of data between database and application, if yes, can you refactor it to limit the time spent talking to the database, or can you batch up the processes.
3 - Considered using SQL Server 2005: Express Edition? Works much better than Access and it's free.
Sig cops got me...
|
|
|
|
|
Sorry, I wasn't looking for the answers you gave. My question is, why should .NET be so much slower than the old app with MFC/ODBC? Even if the performance does not matter or there are better solutions, I still would like to know what makes .NET slow in this case.
|
|
|
|
|
Probably the abstraction provided by .NET, if you take a look at the code in reflector for the System.Data namespace then a simple call could actually make several calls. Reason behind is that you'll gain greater flexibility at the cost of performance however the gain in flexibility is far greater than the loss of performance for the majority of cases.
MFC/C++ may provide this flexibility as well but they tend to (I will stand corrected though) do most of the evaluation at compile-time rather than at JIT/runtime like .NET.
Sig cops got me...
|
|
|
|
|
Hi
How to increase size of database in SQL Server 2000.
Using Enterprise Manager.
Thanking u.
prakash sawant
|
|
|
|
|
Open the database Properties. From the Data Files page you can set File size for your database.
Regards.
_____________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
Don't know why someone gave you a 1 as your direction, though cryptic, is correct.
To accomplish with T-SQL one would execute the following:
<br />
ALTER DATABASE dbFoo<br />
MODIFY FILE<br />
{Name= dbFoo,<br />
SIZE=25MB,<br />
MAXSIZE=2GB,<br />
FILEGROWTH=0)<br />
"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky
|
|
|
|
|
Hi all,
i want to develop a client -server application wich have a frequently access to the database..Using dataset, is it the best way to do it? can i have any problems when many users connect in the same time?if yes, how can i resolve those confilcts?
Thanks for all..
|
|
|
|
|
baxter_dev wrote: Using dataset, is it the best way to do it?
It depends on what you want to do.
baxter_dev wrote: can i have any problems when many users connect in the same time?if yes, how can i resolve those confilcts?
Yes. You can resolve these problems by:
* Restructuring your database.
* Writing more efficient queries
* Upgrading the hardware on the server
* Upgrading the server software to Enterprise Edition.
* etc.
It really depends on what you are doin. There is no one catch-all answer to your question.
|
|
|
|
|
thanks for your answer..
i will try to explain more..i'm developping a financial application (quotes, markets, charts,financial transaction,portfolio...)this application is no a webApp, but server-client..many users will use it at the same time..
data are very importants and can change at any moment ( prices for example)..for getting or setting them from the database, i'm thinking about using datasets, but i know that they work in disconnected mode, so perhaps it will be some problems when updating data for example...so,datasets are they the best way? can i use flags in database to check if the data has been changed before?or doing an other thing?
i hope that it's more clear know..
thanks again and sorry for my poor english
-- modified at 5:49 Wednesday 18th October, 2006
|
|
|
|
|
The datasets use an optimistic concurrency scheme which means that it checks for changes to the tables before updating the database, I seem to remember that some event is fired off so that you can decide what to do in those cases.
It might be worth thinking if you can restructure the way data is handled so that it works in a more disconnected mode, it will save resources on the server for one thing, it fits with the .NET way of doing things and will likely to be more future proof (whatever that means).
Sig cops got me...
|
|
|
|
|
How to make DataSet transparently remoted, or proxied, or called by ref?
I have some DataSets that work in main AppDomain of application,
(it is huge, so I want to connect to it, not load each time)
but I want to move it to separate server, so in one mode I want to place
DataSet in separate app and in second mode- to use it in main AppDomain.
Usual way for it to have object inherited from Marshal-by-ref,
but DataSet already inherited from by Value,
so simple approach is not working,
so question is-
can I create transparent proxy for my Dataset, custom or not - by else way?
Thanks you
|
|
|
|
|
Hello. Is it possible to use SQL Server 2000 Notification Services together with .NET 2.0 client. Thanks in advance.
|
|
|
|
|
Hi
How can I save a DateTime from my C# program into a SQL Server (datetime)
database column.
Shahzad
|
|
|
|
|
Hi,
directly in the Database via a SQL-Statement or into the DataSet ?
With a SQL_Statement you need to format the DateTime into a string ( in the format of the Database-DateTime f.ex. 2006-06-18)
The String you place into the SQL-Statement
|
|
|
|
|
i m using followint store proc but the SET DATEFORMAT not working
have any Idea!!
ALTER PROCEDURE [dbo].[sp_acc_Ins_Vouchers]
(
@voucherNo char(4),
@voucherDate datetime,
@voucherTypeCode char(4),
@voucherKey char(17),
@chequeNumber varchar(20)=null,
@chequeDate datetime=null,
@Posted bit=1,
@Cancelled bit=0,
@createdOn datetime=null,
@createdBy char(5)=null,
@comp_id int,
@Year_id int
)
AS
SET DATEFORMAT ymd
INSERT INTO tbl_acc_Vouchers(voucherNo, voucherDate, voucherTypeCode,
voucherKey,chequeNumber,chequeDate,Posted,Cancelled,createdOn,createdBy,
comp_id,Year_id)
VALUES (@voucherNo, @voucherDate, @voucherTypeCode,
@voucherKey,@chequeNumber,@chequeDate,@Posted,@Cancelled,@createdOn,@createdBy,
@comp_id,@Year_id)
|
|
|
|
|
You should use a SqlParameter object and define a parameter in your query text to match.
|
|
|
|
|
Let SQL server converts string for you:
update mytable set mydate = '10/19/2006' where myid = xxx
|
|
|
|
|
SELECT SO1.name AS Tab, SC1.name AS Col, SO2.name AS RefTab, SC2.name AS
RefCol
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id AND FK.fkey = SC1.colid
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id AND FK.rkey = SC2.colid
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id
WHERE SO2.Name = 'tblcountry'
Rahul
|
|
|
|
|
Hi Rahul,
You could also do this via Enterprise Manager by right clicking the table and viewing dependencies. This is under the assumption that you have sufficient database privileges to execute this action.
Have a great day.
Thanks and Regards,
Aby
|
|
|
|
|
Hi Aby
It was nice to see ur reply.
But its limitation is that it will only give the object that depend on that
table.
But the above queries we can get the Table Name ,Column Name ,dependency On table and Reference Column Name Also.
With Warm Regards
Rahul Joshi
|
|
|
|
|
I have a trade data tables (about 10) and I need to retrieve information based on input parameters. Each table has about 3-4 million rows.
The table has columns like Commodity, Unit, Quantity, Value, Month, Country
A typical query I use to select data is "Select top 10 commodity , sum(value), sum(quantity) , column4, column5, column6 from table where month=xx and country=xxxx"
The column4 = (column2)/(total sum of value) and column 5=(column3)/(total sum of quantity). Column6=column5/column4.
It takes about 3-4 minutes for the query to complete and its a lot of time specially since I need to pull this information from a webpage.
I wanted to know if there is an alternate way to pull the data from server ?
I mean can I write a script that creates tables for all the input combinations i.e month x country (12x228) and save them in table (subtable-table) with a naming convention so from the web I can just pull the table with input parameters mapped to name convention and not running any runtime queries on database ??
OR
Can I write a script that creates a html files for each table for all input combinations save them ?
OR
Is there exists any other solution ?
|
|
|
|
|
Run the query through a SQL Profiler and optimize. 3 to 4 minutes is way to long for such a simplistic query. Just a guess:
CREATE INDEX
MonthCountry_IDX
ON
MyTradeTable
(
Month,
Country
)
I am assuming you have fewer unique [Month] rows than unique [Country] rows.
I would do pre-processing as a last resort. It creates on-going maintenance issues.
|
|
|
|
|
Actual query looks like below:
Column 7 computation is the killer since column7=column5/column4. column4 & column5 part runs twice here. I dont know if I can save column4 & 5 values in a variables and then compute column7 value just using the variables. That will save half of the time. Can it be done ??
Use test
Go
Declare @Country Int, @Month Int
Set @Country=5310
Set @Month=12
select top 10 a.commodity, sum(a.all_qy1_mo) as Quantity, sum(a.all_val_mo) as [Value],
CASE WHEN (select Sum(b.all_qy1_mo)from [2005exp] b
where a.commodity=b.commodity)<>0 THEN (sum(a.all_qy1_mo)/(select
Sum(b.all_qy1_mo) from [2005exp] b
where a.commodity=b.commodity))*100.00 ELSE NULL END as [Column4],
CASE WHEN (select sum(b.all_val_mo) from [2005exp] b
where a.commodity=b.commodity)<>0 THEN (sum(a.all_val_mo)/(select
sum(b.all_val_mo) from [2005exp] b
where a.commodity=b.commodity))*100.00 ELSE NULL END as [Column5],
CASE WHEN sum(a.all_qy1_mo)<> 0 THEN
sum(a.all_val_mo)/sum(a.all_qy1_mo) ELSE NULL END as [Average Price],
CASE WHEN (select Sum(b.all_qy1_mo)from [2005exp] b
where a.commodity=b.commodity)<>0 THEN
(((sum(a.all_val_mo)/(select sum(b.all_val_mo) from [2005exp] b
where a.commodity=b.commodity)))/((sum(a.all_qy1_mo)/(select
Sum(b.all_qy1_mo) from [2005exp] b
where a.commodity=b.commodity)))) ELSE NULL END as [Column7]
from [2005exp] a inner join concord c on a.commodity=c.commodity
WHERE a.cty_code=@Country and a.stat_month=@Month
GROUP BY a.commodity, c.descrip_1, c.quantity_1
order by [Value] desc
|
|
|
|
|
Since you are only looking for the top 10 values, I'd try breaking the SQL statement into smaller chunks.
select top 10 a.commodity,sum(a.all_val_mo) as [Value]
into #tmpCommodity
from [2005exp] a inner join concord c on a.commodity=c.commodity
WHERE a.cty_code=@Country and a.stat_month=@Month
GROUP BY a.commodity, c.descrip_1, c.quantity_1
order by [Value] desc
Then, join your larger SQL to the #tmpCommodity table and get rid of the top 10 (since you already have that). This way, the complex computed columns will not have to calculate for every commodity, but only the 10 you already have determined to be the top values.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
It has to go back to original table (and not just top 10 values), since it has to calculate the sum(a.all_val_mo) without the condition a.cty_code=@country. The column actually calculates the % of their share in compared to all countries.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
