February 3, 2009 at 7:40 am
Now ,A table Table1 have 300 columns and 6000 rows, with Table2 Left Join.
example:
SELECT t1.*,t2.c1,t2.col2
FROM table1 t1 Left Join
(SELECT ID,max(c1) c1,max(c2) c2 FROM table2 GROUP BY ID) t2 on t1.id = t2.id
Run Environment: VS.net 2005 + SQL Server 2005
The Query Write in Front Program,Perform Frequently and Very Waste Resources.
How to Optimize, I would like to ask. Thanks
February 3, 2009 at 7:54 am
Firstly why are you using SELECT *.
do you really need to retrieve every column in the table?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 3, 2009 at 7:58 am
300 columns is a lot for one table, as mentioned don't Select *.
Can you post your table definitions ? for some further advice
February 3, 2009 at 8:11 am
The Code is Writen by Previous Colleagues,I first Come to the Company.
Now Ready to Optimize.
I Have Asked Colleagues, They Said Many Columns of the Table is Used , in order to Convenient , so use "select *".
consider: 1. According to Business Logic,Split column and create many View, Between many view As inner join
2. Replace Left join
February 3, 2009 at 8:15 am
"Convenient " is the devils work....
the general "rule" is that you should only select the columns you need.
Using a view is prob a good idea on such a large table. However if for some reason your view can't be indexed you might find more performance problems (I think)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 3, 2009 at 8:23 am
Part Columns of Table(Others Column is too relate with Cars Properties):
[CarId] [bigint] NOT NULL,
[BrandId] [int] NOT NULL,
[BrandChn] [nvarchar](50) NULL,
[BrandEng] [nvarchar](100) NULL,
[BrandOrigin] [nvarchar](50) NULL,
[MakeAssembly] [nvarchar](50) NULL,
[Make] [nvarchar](50) NULL,
[MakeShort] [nvarchar](50) NULL,
[SeriesId] [int] NOT NULL,
[SeriesChn] [nvarchar](50) NULL,
[SeriesEng] [nvarchar](50) NULL,
[Trim] [nvarchar](50) NULL,
[IsNational] [nvarchar](5) NULL,
[Year] [smallint] NULL,
[EngCode] [nvarchar](20) NULL,
[EngName] [nvarchar](50) NULL,
[VIN] [nvarchar](50) NULL,
[Cylinders] [tinyint] NULL,
[Displacement] [float] NULL,
[Bore] [float] NULL,
[Stroke] [nvarchar](50) NULL,
[CompRatio] [float] NULL,
[FuelType] [nvarchar](10) NULL,
[FuelRate] [nvarchar](50) NULL,
[FuelInduction] [nvarchar](10) NULL,
[ValveTrain] [nvarchar](30) NULL,
[ValsPerCyl] [smallint] NULL,
[ValveTotalNum] [tinyint] NULL,
[HorsePower] [nvarchar](30) NULL,
[Torque] [nvarchar](30) NULL,
[MaxSpeed] [smallint] NULL,
[Transmission] [nvarchar](20) NULL,
[TranType] [nvarchar](20) NULL
February 3, 2009 at 8:28 am
The first thing you should do is go through the .Net code and find out what columns are actualy being used, i don't believe the application is using them all and convience is not really an excuse , it may take a while but it will give you a good basis for further tuning.
February 3, 2009 at 1:17 pm
In addition do you need all 6000 rows ?
Can you "page" through them ?
* Noel
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply