SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


optimize queries


optimize queries

Author
Message
sqlride
sqlride
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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]
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84547 Visits: 41064
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sqlride
sqlride
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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]
Dave Ballantyne
Dave Ballantyne
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2866 Visits: 8370
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84547 Visits: 41064
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sqlride
sqlride
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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.
Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search