|
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.
|
|
|
|
|
miamikk wrote: It has to go back to original table, since it has to calculate the sum(a.all_val_mo) without the condition a.cty_code=@country.
That could also be put in a separate query to speed things up. But no sense belaboring the point, it looks like you and Michael Potter have got it figured out.
--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
|
|
|
|
|
My statement of using a profiler still stands. You have multiple corelated sub-queries which also need to be profiled. You may find out that processing it in multiple queries will be faster.
Hopefully, you have referencial integrity on the [commodity] keys in the [2005exp] and [concord] tables. That will take care of the necessary keys there. If not, you need to add indexes on the [commodity] columns in both tables.
Here is an in memory temporary table that will you can use to remove all of the correlated sub-queries that may be slowing down your query. Just add an additional inner join to your orignal query.
DECLARE @tbl TABLE
(
commodity INTEGER NOT NULL,
all_qy1_mo_commodity_total INTEGER NULL,
all_val_mo_commodity_total INTEGER NULL
)
INSERT INTO @tbl
(
commodity,
all_qy1_mo_commodity_total,
all_val_mo_commodity_total
)
SELECT
commodity,
SUM(all_qy1_mo),
SUM(all_val_mo)
FROM
[2005exp]
GROUP BY
commodity
I am sure it contains some typos but, the final query would look something like this:
select top 10 a.commodity, sum(a.all_qy1_mo) as Quantity, sum(a.all_val_mo) as [Value],
CASE WHEN t.all_qy1_mo_commodity_total <> 0)<>0
THEN (sum(a.all_qy1_mo)/(t.all_qy1_mo_commodity_total))*100.00
ELSE NULL END as [Column4],
CASE WHEN t.all_val_mo_commodity_total<>0
THEN (sum(a.all_val_mo)/(t.all_val_mo_commodity_total))*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 t.all_qy1_mo_commodity_total<>0 THEN
(((sum(a.all_val_mo)/(t.all_val_mo_commodity_total)))/((sum(a.all_qy1_mo)/
(t.all_val_mo_commodity_total)))) ELSE NULL END as [Column7]
from [2005exp] a inner join concord c on a.commodity=c.commodity
inner join @tbl t on a.commodity = t.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
Notice that it is a lot cleaner and the query engine doesn't have to repeat the subqueries muliple times for each row. You will still need an index on [Month], [Country] in the [2205exp] table.
|
|
|
|
|
After inserting an index on commodity and country column, the execution time has been reduced from 3-4 minutes to 10-15 seconds.
I tried the memory table, but I get an error "Column '@tbl.all_qy1_mo_commodity_total' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause".
|
|
|
|
|
My bad - that is what happens when you code without testing. Yes, the resulting columns in the THEN clauses would have to be in the aggregates.
Remove the INNER JOIN for the @tbl. Back to the Correlated Sub-queries but, on a much smaller table. I still think it will be much faster. Your first CASE would look like:
CASE
WHEN ((SELECT all_qy1_mo_commodity_total FROM @tmp where commodity = a.commodity) <> 0)
THEN (sum(a.all_qy1_mo)/
(SELECT all_qy1_mo_commodity_total FROM @tmp where commodity = a.commodity))*100.00
ELSE NULL
END as [Column4]
When you created the index on the [country] column, did you include the [month] column in the same index? It should go from most restrictive to least restrictive to make sure that the query engine examines the least amount of index keys/rows as necessary.
There should be no reason why you can't get this query down to a second or so.
|
|
|
|
|
My database server is too slow by no reason. I guess it is because the LOG file has become too large. I am looking for a stored procedure that clears the LOG file. Someone please help me with its name.
|
|
|
|
|
If you're talking about your transaction log, the only supported way of clearing it is to back it up. Space taken by committed transactions in the log will then be marked as reusable. The log will then reuse that space.
You should perhaps consider which Recovery Model you are using. The Full recovery model is generally recommended for any production system - it permits you to recover to the point of failure if the database files become corrupted or unavailable but you have a full backup and the transaction log is still OK. It also permits you to roll back to any point in time. The Simple recovery model only allows you to restore a full backup; you cannot make transaction log backups.
To reduce the size of the files, use the Shrink Database feature. You will probably need to select the 'Move pages to beginning of file' option to actually have much effect, since the pages that are most likely to be in use straight after doing this will be those at the end of the file.
All that said, a large transaction log does not generally cause a slowdown - SQL Server simply writes to the end of it, expanding the file if necessary. If the disk is starting to get full, the OS may be struggling to find free space for it to expand to. Best practice on a production server is for the transaction log to live on a drive (preferably a RAID 1 [mirrored] array of drives, for redundancy) separately from everything else. This prevents the drive heads from having to seek away from the current write position in the transaction log - log accesses are predominantly sequential-write except when a rollback occurs.
Another common reason for slowdowns as the amount of data increases is simply that your queries and indexes are poorly designed. Most likely one or more tables is having to be scanned - read from beginning to end - rather than using an index to find the rows required. This operation scales more-or-less linearly with the size of the table, getting progressively slower as you add more data. You should always have a representative amount of data in your database when performing pre-production tests, to ensure you catch these problems early.
|
|
|
|
|
Hello,
ive to call a stored procedure using c#. The procedure takes a couple of parameter and returns an out parameter as a cursor.
I take this cursor to show in a datagrid directly for example which runs fine so far. But now i have to make a selection of this cursor, meaning that i want to make a select statement on that cursor selecting specific fields and in a specific order.
Please, can someone tell me how to achieve this?
Best ragards
Trollpower
|
|
|
|
|
Access has had some concurrency problems when used with web applications. You are probably better off with SQL Express.
|
|
|
|
|
I would also recommend SQL Express. Access is probably not too small, but it's more difficult to port and it's not overly extensible. I don't care for Access and I try not to use it when I can help it.
Steve Hanson
Tasen Software
New Hampshire (NH) Website Design & Software Development
|
|
|
|
|
I'm developing a network app for storing login info for access to the internet. Its a fairly small app so i'm wondering is SQL Express overkill or is Access to small.....if you understand what i'm saying!!! 
|
|
|
|
|
If you use SQL Express you can scale up to a full server edition if you need to without much effort. If you use access then it would take more work to migrate.
|
|
|
|
|
I'll probably never scale up to server edition, cant see the app developing that big.
|
|
|
|
|
Also, you need to consider your ISP. Do they host SQL Server? Do they charge extra for this service.
"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
What does the brackets around column names mean??
SELECT [Data] FROM Table
Please help
|
|
|
|
|
The brackets are used to escape any syntax that might otherwise be confused with a SQL keyword, or to allow spaces to appear in a column name.
In some contexts Data is a SQL keyword, but not actually in the column list of a SELECT statement. However, the brackets don't do any harm, so you can leave them in.
|
|
|
|
|
Signifies that the code elements between the square brackets can optionally appear in the SQL query, but are not required. Note that these brackets are not part of the code and must not appear in the SQL query.
|
|
|
|
|
Actually the brackets can appear in the SQL query. They just signify some text string that is its own entity, such as a table name composed of two words with a white space delimiter. You could also use it in a query like this:
SELECT columns FROM table WHERE column LIKE '%[%]%'
Which essentially escapes the middle percent sign, treating it like a percent sign instead of a wildcard character. I hope this helps.
Steve Hanson
Tasen Software
New Hampshire (NH) Website Design and Software Development
|
|
|
|
|
What I am currently doing now is trying to have a backup on database and transaction log on databaseA. But when i tried to restore the transaction log of databaseA to databaseB. It occurred an error.
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'Test_Remote' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
How do i resolve this problem? Such that if i restore transaction log from one database to the other, it won't have an error?
Thanks
|
|
|
|
|
This cannot be done. Transaction logs are associated with a specific database and cannot be applied to a different database.
|
|
|
|
|
select *
from TABLE1 A
INNER JOIN TABLE2 B
ON A.STUDENT_ID = B.STUDENT_ID
INNER JOIN TABLE3 C
ON A.CLASS_ID = C.CLASS_ID
INNER JOIN TABLE4 E
ON A.FORM_ID = E.FORM_ID,
TABLE5 D
INNER JOIN E
On D.SCHOOL_ID = E.SCHOOL
I want to use TABLE1 A to inner join on TABLE2 B, TABLE3 C, TABLE4 E and the TALBE4 E inner join on TABLE5 D. But I get an error
"Invalid object name 'E'."
Thanks for helping
|
|
|
|
|
Oh. I get the answer la. It should be
select *
from TABLE1 A
INNER JOIN TABLE2 B
ON A.STUDENT_ID = B.STUDENT_ID
INNER JOIN TABLE3 C
ON A.CLASS_ID = C.CLASS_ID
INNER JOIN TABLE4 E
ON A.FORM_ID = E.FORM_ID
INNER JOIN TABLE5 D
On D.SCHOOL_ID = E.SCHOOL
Thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
