|
If, after you have used the piebalds solution and checked the hard disks for space and your memory usage on the server, it is still a problem, turn on SQL profiler and run the reports, then analyse the action plan an determine if you have a problem with the queries.
If after optimising your queries you still have an issue do some research into Parameter Sniffing, this is a long shot.
If you still have a problem then hire a DBA consultant to look at your database!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
I deleted my last question since I feel like I've made some kind of progress. However, I'm still not sure how to group all of the data together. I have five tables that hold data. What I am doing is selecting:
UserId, ProductNum, Qty
into a temporary table for each master data table. Here's an example of what each temp table looks like:
John PART-NUM-ONE 5
Jane PART-NUM-ONE 8
Jane PART-NUM-TWO 4
Jack PART-NUM-THREE 9
I have five tables structured just like that, except the Part Number and Qty will be different in each table.
How can I build a query to group by Name, then by Part Number, and a column for each temp table (e.g. Passed, Finished, Scrapped)? I just can't seem to combine them. The Part Numbers come from a Parts table, which stores the Part Number.
djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.
|
|
|
|
|
I came a bit late to the problem. Can you confirm that in your example there are 4 temporary tables? Is this going to be static? (Always 4 Users)
The solution appears to be using a series of Select statements with UNION.
|
|
|
|
|
Well, I forgot to elaborate the end result I am going for. It is not a limit of users. The temp tables store every user's numbers for a particular piece of the process (Pass, Finished, Scrap, etc.). The end result I'm looking for is like this:
Name Part Passed Finished Scrap
-----------------------------------------------
John P-ONE 8 5 7
Jane P-ONE 4 6 10
Jane P-TWO 9 2 3
Jack P-THREE 10 12 6
Bring in the data from ALL temp tables into a group of columns, I guess sort of like a pivot. However, I can't seem to implement any kind of pivot in SQL that solves the problem.
djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.
|
|
|
|
|
I would use a pivot
SELECT UserId,ProductNum,Passed,Finished,Scrapped
FROM
(SELECT UserId, ProductNum, Qty, 'passed' as status
FROM Passed
UNION
SELECT UserId, ProductNum, Qty, 'finished' as status
FROM finished
UNION
SELECT UserId, ProductNum, Qty, 'scrapped' as status
FROM scrapped
) AS SourceTable
PIVOT
(
sum(qty)
FOR status IN ('passed', 'finished','scrapped')
) AS PivotTable;
I havent tested this, but the principle should work.
I also have the opinion that you should normalize the database, several tables with the same content isn't good.
Put them together into one table with a status column.
People say nothing is impossible, but I do nothing every day.
|
|
|
|
|
Thank you, Jorgen. I will give that a try in the morning and I will provide an update.
djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.
|
|
|
|
|
I agree with Jorgen, Pivot is you best option, and the database should be normalised, the design is rubbish!
Alternatively you could do this:
Select Name, Part, 1 Passed,0 Finished,0 Scrap
from PassedTable
UNION
Select Name, Part, 0 Passed,1 Finished,0 Scrap
from FinishedTable
UNION
Select Name, Part, 0 Passed,0 Finished,1 Scrap
from ScrapTable
Group and sum the results as required.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How is it not normalized? We must keep up with each movement for historical purposes. The temp tables only contain the same type of data because I cannot figure out how to make it work with the core tables.
djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.
|
|
|
|
|
Matt U. wrote: How is it not normalized Because you have five tables using the same columns and key
Consider the following design instead:
UserId
ProductNum
Status
Qty
Where status is Passed, Finished, Scrapped and so on. (Or rather a reference from a status table.)
And if you need just the scrapped products you could use a view instead of a table for that purpose.
CREATE VIEW Finished AS
SELECT UserId,ProductNum,Qty
FROM mytable
WHERE status = 'finished'
How does the core tables look like if they present such a problem?
People say nothing is impossible, but I do nothing every day.
|
|
|
|
|
Well, I implemented the UNION suggestion and it works perfectly. And thanks to that solution I should not have any problem using the same concept with the raw tables. It wasn't the fact that I don't have any structure to it (but I don't claim to be an expert DBA or anything ). I've only had experience with joins, not unions and pivots and such.
I appreciate all of the suggestions and input. I've got it working exactly how I needed it to. Thanks again, Jorgen and Mycroft.
djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.
|
|
|
|
|
You're welcome.
People say nothing is impossible, but I do nothing every day.
|
|
|
|
|
Mycroft Holmes wrote:
Select Name, Part, 1 Qty Passed,0 Finished,0 Scrap
from PassedTable
UNION
Select Name, Part, 0 Passed,1 Qty Finished,0 Scrap
from FinishedTable
UNION
Select Name, Part, 0 Passed,0 Finished,1 Qty Scrap
from ScrapTable FTFY
This is probably more efficient than a normal Pivot.
I'd use your suggestion instead.
People say nothing is impossible, but I do nothing every day.
|
|
|
|
|
dear my question about combo box in adoo.net via vb.net
I have Access database contain 3 tables for University colleges and its departments the first tables include the name of college (college of engineering, college of science,…etc.) while second table include the department of college of engineering 3rd table include department of college of science
So who can I do this if I select from combo box 1 for example college of engineering the combobox2 change automatically to table 2 to display only department of college of Eng.
note : the combobox1 bind to table1 by connect ado.net to database using wizard not code
please check this snap shot which figure out more clearly what i need
http://www.iraqeon.net/uploads/1359400696561.jpg[^]
|
|
|
|
|
|
I'm using SMO (SQL Server Management Objects). Can I get table records i.e. (table data) using SMO like I can do it with ADO.NET? For example in DataTable or other form.
Thanks!
|
|
|
|
|
|
Thanks for reply. But it is not actually I'm looking for. I'll try to clarify. Assume we have table "Users":
| Id | Name | Pass |
| 1 | Kate | qwerty |
| 2 | Mike | asd$f5 |
I just want to get a value from second row and column "Name". In this case I'll get value "Mike".
It's equal to query SQL:
"SELECT Name FROM Users WHERE Id=2"
I know that I can use ADO.NET, but in this case I must use SMO.
Can anybody help me?
|
|
|
|
|
SMO isn't meant to be abused as a query-language. Why "must" you use SMO?
|
|
|
|
|
Here, a similar question[^] discussed.
It says: Have a look at SMO's Scripter class[^]. The following basic sample works for me:
using System.Data.SqlClient;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
namespace SqlExporter
{
class Program
{
static void Main(string[] args)
{
var server = new Server(new ServerConnection {ConnectionString = new SqlConnectionStringBuilder {DataSource = @"LOCALHOST\SQLEXPRESS", IntegratedSecurity = true}.ToString()});
server.ConnectionContext.Connect();
var database = server.Databases["MyDatabase"];
var output = new StringBuilder();
foreach (Table table in database.Tables)
{
var scripter = new Scripter(server) {Options = {ScriptData = true}};
var script = scripter.EnumScript(new SqlSmoObject[] {table});
foreach (var line in script)
output.AppendLine(line);
}
File.WriteAllText(@"D:\MyDatabase.sql", output.ToString());
}
}
}
|
|
|
|
|
Thanks all for your replies! I'm realized that it was not adequate question. I'm going to use one of these methods (just like in ADO.NET )
Server.ConnectionContext.ExecuteReader
Server.ConnectionContext.ExecuteWithResults
Sorry for taking your time.
|
|
|
|
|
please ignore my poor english...
I Need Reporting This Table in this shape.plz help me...
SQL Table
----------
City Value
-- --
Londen 20
Tehran 12
Paris 15
Londen 12
My Needed Report
----------
Londen Tehran Paris
---- ---- ----
32 12 15
modified 26-Jan-13 8:52am.
|
|
|
|
|
You need to use SQL PIVOT:
MSDN: Using PIVOT and UNPIVOT[^]
Try:
SELECT
'TotalValue' AS Sum_Total_Values_By_City, [London], [Tehran], [Paris]
FROM
(SELECT City, Value
FROM MyTable) AS SourceTable
PIVOT
(
SUM(Value)
FOR City IN ([London], [Tehran], [Paris])
) AS PivotTable
|
|
|
|
|
Morning All,
I am trying to produce a query for a report that concatenates data from multiple rows. Easiest if I explain what I have, and what I need to end up with! This is Sage Line 500 data by the way.
The table I am querying contains sale order lines as follows:
order_no order_line_no line_type product long_description
100001 1 P 47020 1st description for 47020
100001 2 C 2nd line description for 47020
100001 3 P 47030 1st description for 47030
100001 4 C 2nd line description for 47030
100001 5 P 47040 1st description for 47040
100001 6 C 2nd line description for 47040
100001 7 P 47050 1st description for 47050
100001 8 C 2nd line description for 47050
100001 9 C 3rd line for 47050
So the lines with a line_type 'C' are a continuation line for the description. The problem is they are just numbered sequencially and there could be any number of them.
I want to end up with a results set something like this:
order_no order_line_no line_type product long_description
100001 1 P 47020 1st description for 47020 2nd line description for 47020
100001 3 P 47030 1st description for 47030 2nd line description for 47030
100001 5 P 47040 1st description for 47040 2nd line description for 47040
100001 7 P 47050 1st description for 47050 2nd line description for 47050 3rd line for 47050
Any help or pointers on where to start looking greatly apreciated.
Thanks,
Alex
|
|
|
|
|
Alex Lush wrote: Any help or pointers on where to start looking greatly apreciated.
I'd do this using a console-app; reading from the source-table, iterating order_no's, fetching them completely (the C's), and write the result to a destination-table.
You could also omit the line_type column in the destionation-table; they'd all be 'P', making it a redundant statement.
Whoever designed the structure should be introduced to "Normalization".
|
|
|
|