October 20, 2009 at 3:32 pm
Hi al DB's
I`ve a some trubles with a one table, this table has 7314 rows, when i execute a simple query (Select * from mytable with(nolock)) the time to show all the rows is 18 seconds I'd like to reduce the time, i've a index clustered, a nonclustered, yesterday i executed the odbcc reindex.
this is some part of my table ....
I was thinking in partition the table....
thanks in advance
CONSTRAINT [PK_SubClasesMateriales] PRIMARY KEY CLUSTERED
(
[IdSubClasesMateriales] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [MAYALUMM].[SubClasesMateriales] WITH CHECK ADD CONSTRAINT [FK_SubClasesMateriales_ClaseMateriales] FOREIGN KEY([IdClasesMateriales])
REFERENCES [MAYALUMM].[ClaseMateriales] ([IdClaseMateriales])
GO
ALTER TABLE [MAYALUMM].[SubClasesMateriales] CHECK CONSTRAINT [FK_SubClasesMateriales_ClaseMateriales]
October 20, 2009 at 5:37 pm
You shouldn't have to partition anything for a 7k row table because it won't help when returning all rows and columns.
Something is dreadfully wrong if it takes that long to return just 7k rows... please post the CREATE TABLE statement and also tell us how far away you are from the actual server.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2009 at 5:48 pm
Hi Jeff, thanks a lot...
Now that you are talking something about the WLAN, when i execute this query in the local server the time is 1 second, but when i execute the query in other computer in another office loke 1kilometer from the server the time too bad!.....
thankyou Jeff!!!
this is my create
CREATE TABLE .[SubClasesMateriales](
[IdSubClasesMateriales] [int] IDENTITY(1,1) NOT NULL,
[IdClasesMateriales] [int] NOT NULL,
[NoSubClase] [int] NULL,
[NoTerminacionClase] [int] NULL,
[Subclase] [varchar](25) NULL,
[Descripcion] [varchar](1000) NULL,
[Lote] [char](25) NULL,
[Activo] [nchar](1) NULL,
[FechaSistema] [smalldatetime] NULL,
CONSTRAINT [PK_SubClasesMateriales] PRIMARY KEY CLUSTERED
(
[IdSubClasesMateriales] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [MAYALUMM].[SubClasesMateriales] WITH CHECK ADD CONSTRAINT [FK_SubClasesMateriales_ClaseMateriales] FOREIGN KEY([IdClasesMateriales])
REFERENCES [MAYALUMM].[ClaseMateriales] ([IdClaseMateriales])
GO
ALTER TABLE [MAYALUMM].[SubClasesMateriales] CHECK CONSTRAINT [FK_SubClasesMateriales_ClaseMateriales]
October 20, 2009 at 10:33 pm
alopeze (10/20/2009)
Now that you are talking something about the WLAN, when i execute this query in the local server the time is 1 second, but when i execute the query in other computer in another office loke 1kilometer from the server the time too bad!.....
So what sort of network do you have between yourself and the server ?
In terms of query tuning , there really is nothing that can be done.
Do you have a realistic expectation of the speed you need, is this a real world production issue ?
You may want to look into one of the replication options.
October 21, 2009 at 12:29 am
alopeze (10/20/2009)
Hi Jeff, thanks a lot...Now that you are talking something about the WLAN, when i execute this query in the local server the time is 1 second, but when i execute the query in other computer in another office loke 1kilometer from the server the time too bad!.....
thankyou Jeff!!!
this is my create
CREATE TABLE .[SubClasesMateriales](
[IdSubClasesMateriales] [int] IDENTITY(1,1) NOT NULL,
[IdClasesMateriales] [int] NOT NULL,
[NoSubClase] [int] NULL,
[NoTerminacionClase] [int] NULL,
[Subclase] [varchar](25) NULL,
[Descripcion] [varchar](1000) NULL,
[Lote] [char](25) NULL,
[Activo] [nchar](1) NULL,
[FechaSistema] [smalldatetime] NULL,
CONSTRAINT [PK_SubClasesMateriales] PRIMARY KEY CLUSTERED
(
[IdSubClasesMateriales] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [MAYALUMM].[SubClasesMateriales] WITH CHECK ADD CONSTRAINT [FK_SubClasesMateriales_ClaseMateriales] FOREIGN KEY([IdClasesMateriales])
REFERENCES [MAYALUMM].[ClaseMateriales] ([IdClaseMateriales])
GO
ALTER TABLE [MAYALUMM].[SubClasesMateriales] CHECK CONSTRAINT [FK_SubClasesMateriales_ClaseMateriales]
Yeah... that's a tiny table. It should return almost instaneously. You'll need to talk with whoever is in charge of your network and WAN/LAN... depending on the comm line between the sites, there may be nothing you can do about this. I just went through the same thing... takes a very complicated proc only 12 seconds to run and the server and return the result set on the server... takes 139 seconds for it to make it from NY to Detroit. Company before that had the same problem. The comm lines were just terrible.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2009 at 7:57 am
Hi Jeff once egain...
interesting your case and in that case who u can resolve it?... 🙂
And i'll talk with the network administrator.
I really apreciate it..
Tx.
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply