Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

optimize queries Expand / Collapse
Author
Message
Posted Tuesday, October 20, 2009 3:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 6, 2014 2:27 PM
Points: 12, Visits: 196
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]



Post #806136
Posted Tuesday, October 20, 2009 5:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 35,534, Visits: 32,117
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #806191
Posted Tuesday, October 20, 2009 5:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 6, 2014 2:27 PM
Points: 12, Visits: 196
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]


Post #806197
Posted Tuesday, October 20, 2009 10:33 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
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.




Clear Sky SQL
My Blog
Kent user group
Post #806248
Posted Wednesday, October 21, 2009 12:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 35,534, Visits: 32,117
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #806276
Posted Wednesday, October 21, 2009 7:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 6, 2014 2:27 PM
Points: 12, Visits: 196
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.
Post #806512
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse